Vlookup Approximate Match Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try this array formula** :

A1 = lookup value

C1:D12 = lookup table

=INDEX(D1:D12,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)
 
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.
 
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.
 
T. Valko said:
Try this array formula** :

A1 = lookup value

C1:D12 = lookup table

=INDEX(D1:D12,MATCH(MIN(ABS(C1:C12-A1)),ABS(C1:C12-A1),0))
Click to expand...
....

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,D2:D12),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.
 
Back
Top