D

#### dlotz

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

D

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

R

check this out:

http://www.contextures.com/xlFunctions03.html

T

how do I go brackwards

Backwards from what?

What do you want to lookup?

B

Please explain a little more

best wishes

best wishes

D

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

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

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

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

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