Vlookup Approximate Match Question
"T. Valko" wrote...
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 can be done without array formulas as long as C1:C12 is sorted in
ascending order.
=IF(2*A1C1+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.
|