# table lookup

• I have a row with 10 different escalating numbers (in numerical order lowest to highest). Example: Cell B1=10, C1=20, D1=30, etc.

If I have a value in A1 of 21.4, I want to do a lookup in that row for the number higher than 21.4 (which would be 30) and dump the number 30 into cell A2.

I have tried hlookup and it works fine except it returns a number LOWER than the value I input rather than HIGHER. How can I modify the hlookup command to return the higher value which will ALWYS be one cell or column to the right?

• Using Index+Match

Table must in descending order
i.e. B1=30, C1=20, D1=10

=INDEX(B1:D1,1,MATCH(A1,B1:D1,-1))

returns larger value than the test value in the list

• If the table is in ascending order, you could try this:

=MATCH(A1,B1:K1)+ISERROR(MATCH(A1,B1:K1,0))

• Or, if you would prefer not to change your table...

=IF(ISNA(MATCH(A1,B1:G1,0)),INDEX(B1:G1,MATCH(A1,B1:G1)+1),INDEX(B1:G1,MATCH(A1,B1:G1,0)))

Hope this helps!

• Thank you all for your help. I actually have a much more complex table than the one I mentioned. All of your ideas helped me to find how to make it work the best with what I am looking for. Thanks again.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!