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.Ok, go ahead, I will give you a minute to soak in the awesomeness of that formula.Īre you back?, well, lets explore what this formula does. The start and end values are in B6:B15 and C6:C15 respectively,.Assuming the value to be looked up is in cell C3.Then I thought, “may be SUMPRODUCT formula would work for situations like these?!?”Īfter playing for a while, I got the perfect formula for this. So I naturally turned to a cup of home brewed coffee (remember, I no longer work in a office, so I cant 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). MATCH formula looks up a value and tells the position of it in a listīut neither seem to solve this problem.VLOOKUP 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.
0 kommentar(er)
