vlookup backward

  • Thread starter Thread starter dlotz
  • Start date Start date
D

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
 
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
 
Sorry, still not clear.
OK the first reference array is
A B C
Dallas 1234 Bob J
Click to expand...

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)
Click to expand...

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.

?????
 
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
 
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.
 
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))
 
Back
Top