When you SUMPRODUCT this with above you get a single number corresponding the row in which the match occurred

ROW(B6:B15) part: This just gives running numbers from 6 to 15.

The output would be a bunch of 0s with probably a single 1 (B6:B15=C3) part: This is checking the range B6:B15 and C6:C15 to find that one set of start and end values that would contain the value in C3.Since we just want to know which row will contain the value, we can use XMATCH as shown below. So I naturally turned to a cup of home brewed coffee (remember, I no longer work in a office, so I can’t rush to espresso machine) and stared long and hard out of the window (remember, I no longer go to office, that means I can sit in front of a window and work).Īdded in December, 2022: We can use XMATCH: MATCH formula looks up a value and tells the position of it in a listīut neither seem to solve this problem.XLOOKUP formula looks up a value in a table and returns the corresponding value in next column.

Now, the problem is similar to between formula trick we discussed a few days back, yet very different. In simple words, we have to find the range that has the lookup value. See the below illustration to understand what I mean. The only glitch is that, instead of values, the lookup table contained lower and upper boundaries of the values. Recently I was given a data set like this (shown below) and asked to find the position of lookup value in the list.

How to find a range in excel