VLookup Start from the Right Column and read to the left

D

Darin

Anyone know if it's possible to have the range in a Vlookup begin w/ the upper right most cell and column and read backwards from right to left? Thx.
 
F

Frank Kabel

Hi
use INDEX + MATCH for this. e.g. the following looks in column C for
your lookup criteria and returns the value from column A:
=INDEX(A1:A100,MATCH(lookup_criteria,C1:C100,0))

--
Regards
Frank Kabel
Frankfurt, Germany

Darin said:
Anyone know if it's possible to have the range in a Vlookup begin w/
Click to expand...
the upper right most cell and column and read backwards from right to
left? Thx.
 
J

Jack Sons

Darin,

Use the code below (put it in your personal.xls).

Jack Sons
The Netherlands

Function VLookupLeft(lookupValue, ByVal lookupArray As Range, _
returnValueColumnOffset As Integer, _
Optional lookupValueColumn As Integer = -257)

'This function performs a simple lookup for values
'to the left (as well as to the right) of the
'lefthand column of the lookup array. And with a 4th
'argument added, it allows any column of the
'lookupArray to be used rather than the lefthand
'column. The third argument is the number of columns
'that the return value column is offset from the left-
'hand column of the lookup array. E.g., if the sought
'value is in the second column of the lookup array,the
'the third argument is 1. Columns left of reference column do not
belong to 'the lookup array. If value of J1 is to be found in column
B and to be returned 'is the value of the corresponding cell in column
A, the function should be 'VlookupLeft(J1;B:B;-1)


'Check to confirm that the lookup array is a single
'area range
If lookupArray.Areas.Count > 1 Then
MsgBox "this function accepts only single-area ranges"
Exit Function
End If

With Application

'If no other column for the lookup value was input,
'return the value matching the lookup value in the
'lefthand column of the lookup array
If lookupValueColumn = -257 Then
VLookupLeft = _
.Index(lookupArray.Offset(0, returnValueColumnOffset), _
.Match(lookupValue, lookupArray.Columns(1), 0), 1)

'Otherwise return the value matching the lookup value
'in the input column of the lookup array
Else
VLookupLeft = _
.Index(lookupArray.Offset(0, returnValueColumnOffset), _
.Match(lookupValue, lookupArray.Columns(lookupValueColumn), 0),
1)
End If

End With

End Function


Darin said:
Anyone know if it's possible to have the range in a Vlookup begin w/ the
Click to expand...
upper right most cell and column and read backwards from right to left?
Thx.
 
A

AlfD

Hi!
Does this sound like what you want?


"VLookupLeft can return a sought value or values that appear to th
left of the lefthand column of the lookup array (or range) as well a
those that appear to the right, and can, at the user's option, use
different column of the lookup table than the lefthand column for th
lookup value."

Quote from Alan Beban's webpage


'http://www.home.pacbell.net/beban/ArrayFunctions.xls' (http://)

Al
 
Top