Matching formula results to datasets
 

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

Matching formula results to datasets
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ben Burns" wrote in message
oups.com...
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

Matching formula results to datasets
 

Sort your data in Descending order

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

Adjust to suit


"Ben Burns" wrote:

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

Matching formula results to datasets
 

On 5 Apr, 15:38, Teethless mama
wrote:
Sort your data in Descending order

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

Adjust to suit

"Ben Burns" wrote:
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

This works perfectly. Many thanks.