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.

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.

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

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.