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)

--
Biff
Microsoft Excel MVP


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


--
HTH,

RD

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




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

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.

--
HTH,

RD

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

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


--
HTH,

RD

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




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