vlookup backward

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
 
D

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

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.

?????
 
A

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
 
M

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top