Prev: Counting incidences of surname and date of birth and rearrangi
Next: Searching from a table
From: Gord Dibben on 5 Jul 2008 11:17
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)
<RameshJaiswal9867936636(a)discussions.microsoft.com> 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
>> > >
>> > > sandymann2(a)mailinator.com
>> > > Replace @mailinator.com with @tiscali.co.uk
>> > >
>> > >
>> > > "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal
>> > > (9867936636)@discussions.microsoft.com> wrote in message
>> > > news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com...
>> > > > 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
>> > > >
>> > >
>> > >
>> > >

From: sajay on 7 Jul 2008 09:13
put the formula in sheet 2 b2

=LOOKUP(A1,Sheet1!B1:B4,Sheet1!A1:A4)


fill down
sheet1
12 Raj
55 Abhay
96 Vinod
75 Ganesh

sheet2
Abhay
Vinod
Raj
Ganesh




"Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal
(9867936636)@discussions.microsoft.com> wrote in message
news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com...
> 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

First  |  Prev  | 
Pages: 1 2 3
Prev: Counting incidences of surname and date of birth and rearrangi
Next: Searching from a table