Matching formula results to datasets

[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

 

[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

 

[Guest]

Sort your data in Descending order

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

Adjust to suit

 

[Ben Burns]

Sort your data in Descending order

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

Adjust to suit

This works perfectly. Many thanks.