Matching formula results to datasets

B

Ben Burns

Any help/advice much appreciated...

I have a numerical value produced as a result of an existing formula
and need to find the closest higher value from a predefined list.

Does anyone know how to perform this sort of lookup, with the match
from the pre-defined list as the output?

Thanks
Ben
 
B

Bernard Liengme

If the list is sorted you can use LOOKUP, MATCH or (VLOOKUP or HLOOKUP). But
these find "largest value in the array that is less than or equal to
lookup_value" - assuming an ascending sort. (quote is from XL Help)

So if you calculated value is 2.4 and the list contains 1,2,3,4,5,6 then the
lookup gives you 2 which is fine. But if the calculated value is 2.8 I
expect you will want 3 which is not what the lookup will give.

You will need to do a bit of math to get what you need. Please tell us more
and maybe someone can help (I'll try!)

best wishes
 
T

Teethless mama

Sort your data in Descending order

=INDEX(A1:A7,MATCH(C2,A1:A7,-1))

Adjust to suit
 
Top