0

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))

enter image description here

7
  • 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
  • 1
    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
  • 1
    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
1

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)
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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.