vlookup backward

[dlotz]

col A the city were the employee located,
col B the emploee #1234
Col C the employeee name

OK simple vlookup right,

well on sheet 2 the data is different
col A agent #
Col B agent Name
col C agent city

how do I go brackwards

 

[ryguy7272]

Which of those three elements are you looking for? Shouldn't be a problem;
check this out:
http://www.contextures.com/xlFunctions03.html

 

[T. Valko]

It's not real clear what you want to do.

how do I go brackwards

Backwards from what?

What do you want to lookup?

 

[Bernard Liengme]

Please explain a little more
best wishes

 

[dlotz]

OK the first reference array is
A B C
Dallas 1234 Bob J

On the next sheet the colums have to be in this order, no other allowed by
the company

A b c
1234 Bob J Dallas

in in b1 I have =vlookup(a1, sheet 1 a1:c100,2,false)
no probem
then what nexy??????

in c1 =vlookup(a1, sheet 1! A1:c100,????????????, false)

I can not change the order of the colums and sheet one is were data is added
this multi sheet work book

 

[T. Valko]

Sorry, still not clear.

OK the first reference array is
A B C
Dallas 1234 Bob J

Is that the data on Sheet1?

A b c
1234 Bob J Dallas
in in b1 I have =vlookup(a1, sheet 1 a1:c100,2,false)

Does A1 in the formula refer to 1234? If so, the formula won't work. The
lookup_value 1234 has to be in the leftmost column of the table_array sheet
1 a1:c100.

?????

 

[Ashish Mathur]

hi,

Try this

=vlookup($A2,sheet1!$A$2:$C$101,match(C$1,sheet1!$A$1:$C$1,0), false)

In sheet1, give columns heading in row 1. So the range would become
A2:C101. In sheet2 also, give the headings in row 1. So you will write
your formula in row 2.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[minyeh]

from my understanding,
u have the raw data in sheet1
with column header arranging in order
Col A : AgentCity
Col B : Agent#
Col C : AgentName

then u wanted to lookup using Agent# in sheet2
where
Col A : Agent#
Col B : AgentName
Col C : AgentCity

then, given that the header is in row 1,
and with Sheet2!A:A (Agent#) manually keying in
in Sheet2!B2, key in
=INDEX(OFFSET(Sheet1!$B:$B,0,MATCH("Agent#",Sheet1!$1:$1,0)-MATCH(B
$1,Sheet1!$1:$1,0)),MATCH($A2,Sheet1!$B:$B,0))
copy across to column C, copy down as long as u need

*u can change the "Agent#" to $A$1 if that's where the column header
is

help it helps.

 

[minyeh]

sorry, mistake of my earlier function
should be
=INDEX(OFFSET(Sheet1!$B:$B,0,MATCH(B$1,Sheet1!$1:$1,0)-MATCH
("Agent#",Sheet1!$1:$1,0)),MATCH($A2,Sheet1!$B:$B,0))



another way:

in Sheet2!B2
=INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$B:$B,0),MATCH(Sheet2!B$1,Sheet1!
$1:$1,0))