How can i use lookup for data in reverse column ?

[RJ]

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

 

[Mike H]

Hi,

Try this

=INDEX(Sheet2!$A$1:$A$4,MATCH(A1,Sheet2!$B$1:$B$4,0))

This assumes your data are on sheet 2 and the value you are looking up is in
A1 of another sheet.

Mike

:

> 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
> in another Excel sheet my data is,

> A B

> Abhay

> Vinod

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

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk


news:[email protected]...

> 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
> in another Excel sheet my data is,

> A B

> Abhay

> Vinod

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

 

[RJ]

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



:

> Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 will
> =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2)))
> --
> Sandy

> In Perth, the ancient capital of Scotland

 

[Mike H]

It doesn't work, you cannot Vlookup and return a column to the left

:

> 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

 

[RJ]

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

:

> It doesn't work, you cannot Vlookup and return a column to the left

> > "Ramesh Jaiswal " 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

> >

> >

> > "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 address removed)

> > > Replace @mailinator.com with @tiscali.co.uk

> > >

> > >

> > > news:[email protected]...

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

 

[Mike H]

Hi,

The solution I gave you in my first post does exactly what you require. All
you will have to do is change the ranges to suit your needs.

Mike

:

>
> Any other solution. Please confirm..... If possible plesae provde the

> alternet solution with example in formula..... It will help me a lot.

>

> > "Mike H" wrote: >
> > It doesn't work, you cannot Vlookup and return a column to the left

> >

> > "Ramesh Jaiswal ()" 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

> > >

> > >

> > > "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 address removed)

> > > > Replace @mailinator.com with @tiscali.co.uk

> > > >

> > > >

> > > > news:[email protected]...

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

 

[Gord Dibben]

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

 

[sajay]

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