Can't remember where I got this UDF but give it a try.
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
Gord Dibben MS Excel MVP
On Sat, 5 Jul 2008 05:07:00 -0700, Ramesh Jaiswal (9867936636)
<(E-Mail Removed)> wrote:
>
>Dear Mike,
>
>Any other solution. Please confirm..... If possible plesae provde the
>alternet solution with example in formula..... It will help me a lot.
>
>Ramesh Jaiswal
>9867936636
>
>"Mike H" wrote:
>
>> It doesn't work, you cannot Vlookup and return a column to the left
>>
>> "Ramesh Jaiswal (9867936636)" wrote:
>>
>> > Dear Sandy,
>> >
>> > The Formula provided by you is of Index. I required the Vlookup formula.
>> > As describe i require the lookup in revers i.e. if any data form sheet 2 is
>> > present in second coloum in sheet 1, then i required the 1st data of the
>> > respective field form sheet 1 to be appear in the column 2 of sheet 2.
>> >
>> >
>> > =Vlookup(sheet1!$A$1:$B$8,-1,0)
>> >
>> > Ramesh Jaiswal
>> > 9867936636
>> >
>> >
>> > "Sandy Mann" wrote:
>> >
>> > > Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 will
>> > > do it thus:
>> > >
>> > > =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2)))
>> > >
>> > > --
>> > > HTH
>> > >
>> > > Sandy
>> > > In Perth, the ancient capital of Scotland
>> > > and the crowning place of kings
>> > >
>> > > (E-Mail Removed)
>> > > Replace @mailinator.com with @tiscali.co.uk
>> > >
>> > >
>> > > "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal
>> > > (9867936636)@discussions.microsoft.com> wrote in message
>> > > news:5574D832-10DD-4162-B9CB-(E-Mail Removed)...
>> > > > How can Vlook work to provide the data in revers for example:
>> > > > in one excel sheet my data is,
>> > > > A B
>> > > > 12 Raj
>> > > > 55 Abhay
>> > > > 96 Vinod
>> > > > 75 Ganesh
>> > > >
>> > > > in another Excel sheet my data is,
>> > > > A B
>> > > > Abhay
>> > > > Vinod
>> > > > Raj
>> > > > Ganesh
>> > > >
>> > > > I want the A column to be lookup and the value of the first sheet column A
>> > > > should appear. Is it possible. I know it is possible if i cust the B
>> > > > column & past it in the A column and its work. But i do not want to cut
>> > > > the
>> > > > same.
>> > > > I have tried by no use. Please help me.
>> > > >
>> > > > Ramesh Jaiswal
>> > > > 9867936636
>> > > >
>> > >
>> > >
>> > >
Similar Threads
