Vlookup Approximate Match Question

[Craig]

Hi...

In the Vlookup approximate match situation, (range lookup argument is true)
the function returns a value on the same row to the closest matching value in
a lookup table that is less than or equal to the lookup value...is there
anyway to change it so it returns a value on the same row to the closest
matching value in to the lookup value even though the closest value may be
higher than the lookup value?
thank you!

Craig

 

[T. Valko]

Try this array formula** :

A1 = lookup value

C112 = lookup table

=INDEX(D112,MATCH(MIN(ABS(C1:C12-A1)),ABS(C1:C12-A1),0))

This will return the first instance of the closest value. For example, if
the lookup value is 7 and the lookup table contains 5 and 9, the absolute
difference is the same but 5 is listed first so the match will be with 5.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ragdyer]

Try this *array* formula, with the lookup range in A1 to A50,
the data to be returned in B1 to B50,
and the value to lookup entered in C1:

=INDEX(B1:B50,MATCH(MIN(ABS(A1:A50-C1)),ABS(A1:A50-C1),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

If the lookup value is in the *middle* of 2 values in the lookup range, the
row containing the *smallest* value will be returned.

 

[Ragdyer]

Actually, its the smallest *row* that's returned.

If you would need the *larger* row number to display it's value in the case
of a tie, try this formula, also an *array*:

=INDEX(B1:B5,MATCH(MAX(IF(ABS(A1:A5-C1)=MIN(ABS(A1:A5-C1)),A1:A5)),A1:A5,0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[Harlan Grove]

Try this array formula** :

A1 = lookup value

C112 = lookup table

=INDEX(D112,MATCH(MIN(ABS(C1:C12-A1)),ABS(C1:C12-A1),0))

....

This can be done without array formulas as long as C1:C12 is sorted in
ascending order.

=IF(2*A1>C1+C2,LOOKUP(2*A1-eps,C1:C11+C2:C12,D212),LOOKUP(A1,C1,D1))

where eps is a 'small' positive value like 1E-12. Without it, this would
return the 'higher' col D value if the A1 value were exactly at the midpoint
between some pair of values in col C.