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?

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