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.

--
Biff
Microsoft Excel MVP


"Graeme" wrote in message
...
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.

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))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Graeme" wrote in message
...
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.

"Ragdyer" wrote...
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))
....

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_so ught)
+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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" wrote in message
...
"Ragdyer" wrote...
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))
...

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_so ught)
+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.