vlookup - find an alternative approximate match

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

With vlookup, and a range lookup value of TRUE, when there is no exact match,
is it possible to return the smallest value that is greater than the lookup
value (rather than next largest value that is less than lookup value)? Thank
you.
 
One way...

A1 = lookup_value
G1:H10 = table_array

Array entered** :

=INDEX(H1:H10,MATCH(TRUE,G1:G10>=A1,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

That would be the equivalent of:

=VLOOKUP(A1,G1:H10,2,TRUE)

Except it will return the *next higher* value when an exact match isn't
found.
 
Another way - *non* array option:

With datalist in A1 to B20, and lookup value in C1,
try this:

=INDEX(B1:B20,MATCH(SMALL(A1:A20,COUNTIF(A1:A20,"<"&C1)+1),A1:A20,0))
 
Ragdyer said:
Another way - *non* array option:

With datalist in A1 to B20, and lookup value in C1,
try this:

=INDEX(B1:B20,MATCH(SMALL(A1:A20,COUNTIF(A1:A20,"<"&C1)+1),A1:A20,0))
Click to expand...
....

So close!

The MATCH and SMALL calls are superfluous when the first column of the
lookup range is sorted in ascending order. The general form

=INDEX(table,COUNTIF(INDEX(table,0,1),"<"&value_sought)
+1,column_sought)

and the particular form matching your formula

=INDEX(B1:B20,COUNTIF(A1:A20,"<"&C1)+1)

produce the desired result. Further, these forms work when col A
contains text.
 
Point well taken Harlan!

My test data wasn't sorted, but in retrospect, it should definitely have
been, since the OP's entire question revolved around *inexact* matches.
 
Back
Top