# Look up corresponding variable to first value in list that is larger than lookup value

Please see image for problem description and example.

I want to return the letter corresponding to the first number larger than my lookup value/number.

I've been trying to solve this using index-match but it only seems to work if there's an exact match between the lookup value and the numbers it is compared to.

This is the formula I thought would work but doesn't: =+INDEX(A3:F3,MATCH(A6,A2:F2,-1)) • This may be of use. Another possibility is to use: `MATCH(ROUNDUP(A6,0),A2:F2,0)` – cybernetic.nomad Mar 19 '19 at 14:47
• Thank you - yes this solves the above problem. I should have framed it more generally though: What if it can't be fixed by rounding (e.g. because all or none of the values are integers?) – F Bert Mar 19 '19 at 14:52
• Can you edit your question to provide a complete and representative sample of data? – cybernetic.nomad Mar 19 '19 at 14:56
• @cybernetic.nomad good point. – F Bert Mar 19 '19 at 15:09
• From your description, an exact match should still return the next value, so `1.6` should return `t`. Is that what you want? – AFH Mar 19 '19 at 15:22

## 2 Answers

As discussed in the link I provide in comments, `MATCH(,,-1)` requires data to be sorted in descending order or it will return and error.

To get what you literally stated ("the first number larger than my lookup value/number", you can use:

``````=INDEX(A3:F3,MATCH(A6,A2:F2,1)+1)
``````

To get a match with the first number equal or larger than your lookup value, you can use:

``````=INDEX(A3:F3,IFERROR(MATCH(A6,A2:F2,0)-1,MATCH(A6,A2:F2,1))+1)
``````

Lots of things one could have done here. For something a little more "off the wall" but a shorter formula so... one could use:

``````=INDEX(FILTER(A2:F3,A2:F2>=A6),2,1)
``````

`FILTER()` being set up this way to return the desired letter in the first column of the resultant array (said array just used internally). So one would know precisely that the desired letter is "row 2, column 1" no matter what making the `INDEX()` function easy to fill in.

"Larger than" or "equal to or larger than" is then handled in the way one is much more used to, to wit, using ">" or ">=" in the condition in `FILTER()`.

It does address the case of the lookup row being unsorted (as in out of order) because `FILTER()` will sort its resultant array without being asked. So no matter the condition of the lookup row, the correct answer is returned.

If one does not have `FILTER()` available, one could use the following as the array for `MATCH()` to find and feed `INDEX()` its value:

``````=MIN(IFERROR((A2:F2)/(A2:F2>=A6),MAX(A2:F2)))
``````

Almost completely old-school and one can substitute an `IF(ISERROR(...` if one does not have `IFERROR()` available. The values are being divided by the 1 or 0 from the ">=" test so the values in the lookup row are produced OR an error is, but in those cases, `MAX()` fills in the highest value in the lookup row so it won't interfere with finding the desired value. If needed by circumstances, one could slip a "+1" into the `MAX()` result so all the less than's exceed the "natural" entries.

Of course, nowadays `XLOOKUP()` would do it very nicely:

``````=XLOOKUP(A6,A2:F2,A3:F3,,1)
``````

Short, simple, clear. No tricks needed.

(`F Bert` was happy. Just posting these for anyone researching who needs an answer not wedded to the problem's sole set of factors.)