Complex lookup issue
 

Hi,
Does anyone know how to do a double "lookup" in Excel?

My situation is like this, I want to search a specific data on Column A
(under count), then after it's found I want to search the corresponding
matrix data for that item from the headers of Columns B to E. Then the result
will be put into Column C of my "FILL-IN" worksheet


Example, below is the worksheet (named DATA-REF) I want to look data to:
Column A Column B Column C Column D Column E
count dog cat mouse fish
20 100 monster 300 400
30 trip counta sea data-?
34 99 1001 210 22
100 qwqe asdas gdg jhg
999 qwww 323 sfd 8686


Below is my other worksheet named (FILL-IN) that will search the data in
"DATA-REF" worksheet and fill the result in Column C:
Column A Column B Column C
data Anim data Final data (the column where I need to fill in the data)
20 cat ???? (the lookup result should be monster)
34 mouse ???? (the lookup result should be 210)
999 fish ???? (the lookup result should be 8696)


The "????" in my worksheet "FILL-IN" is the data I'm looking for in the
"DATA-REF " worksheet. So meaning if I lookup "20" on Column A in worksheet
"DATA-REF" after it's found I want to lookup "cat" as per reference to Column
B on my worksheet "FILL-IN" then search the "cat" in "DATA-REF" corresponding
to "20" on Column A of my "DATA-REF" worksheet. So the final lookup result
should be "monster", as what I written there (the lookup result should be
monster).

Sorry if it sounds confusing but that is the best I can do. Pls help!

Thanks!

Complex lookup issue
 

where A1=20 and B1=cat and your data table is on Sheet1, A1:E6 (with column
labels in row 1), try:

=VLOOKUP(A1,Sheet1!A2:E6,MATCH(B1,Sheet1!A1:E1,0), 0)

another way you could do it is
=INDEX(Sheet1!A1:E6,MATCH(A1,Sheet1!A1:A6,0),MATCH (B1,Sheet1!A1:E1,0))

or even
=OFFSET(Sheet1!A1,MATCH(A1,Sheet1!A2:A6,0),MATCH(B 1,Sheet1!B1:E1,0))


"Lorderon" wrote:

Hi,
Does anyone know how to do a double "lookup" in Excel?

My situation is like this, I want to search a specific data on Column A
(under count), then after it's found I want to search the corresponding
matrix data for that item from the headers of Columns B to E. Then the result
will be put into Column C of my "FILL-IN" worksheet


Example, below is the worksheet (named DATA-REF) I want to look data to:
Column A Column B Column C Column D Column E
count dog cat mouse fish
20 100 monster 300 400
30 trip counta sea data-?
34 99 1001 210 22
100 qwqe asdas gdg jhg
999 qwww 323 sfd 8686


Below is my other worksheet named (FILL-IN) that will search the data in
"DATA-REF" worksheet and fill the result in Column C:
Column A Column B Column C
data Anim data Final data (the column where I need to fill in the data)
20 cat ???? (the lookup result should be monster)
34 mouse ???? (the lookup result should be 210)
999 fish ???? (the lookup result should be 8696)


The "????" in my worksheet "FILL-IN" is the data I'm looking for in the
"DATA-REF " worksheet. So meaning if I lookup "20" on Column A in worksheet
"DATA-REF" after it's found I want to lookup "cat" as per reference to Column
B on my worksheet "FILL-IN" then search the "cat" in "DATA-REF" corresponding
to "20" on Column A of my "DATA-REF" worksheet. So the final lookup result
should be "monster", as what I written there (the lookup result should be
monster).

Sorry if it sounds confusing but that is the best I can do. Pls help!

Thanks!

Complex lookup issue
 

Hi, Thanks!!!!!!

"JMB" wrote:

where A1=20 and B1=cat and your data table is on Sheet1, A1:E6 (with column
labels in row 1), try:

=VLOOKUP(A1,Sheet1!A2:E6,MATCH(B1,Sheet1!A1:E1,0), 0)

another way you could do it is
=INDEX(Sheet1!A1:E6,MATCH(A1,Sheet1!A1:A6,0),MATCH (B1,Sheet1!A1:E1,0))

or even
=OFFSET(Sheet1!A1,MATCH(A1,Sheet1!A2:A6,0),MATCH(B 1,Sheet1!B1:E1,0))


"Lorderon" wrote:

Hi,
Does anyone know how to do a double "lookup" in Excel?

My situation is like this, I want to search a specific data on Column A
(under count), then after it's found I want to search the corresponding
matrix data for that item from the headers of Columns B to E. Then the result
will be put into Column C of my "FILL-IN" worksheet


Example, below is the worksheet (named DATA-REF) I want to look data to:
Column A Column B Column C Column D Column E
count dog cat mouse fish
20 100 monster 300 400
30 trip counta sea data-?
34 99 1001 210 22
100 qwqe asdas gdg jhg
999 qwww 323 sfd 8686


Below is my other worksheet named (FILL-IN) that will search the data in
"DATA-REF" worksheet and fill the result in Column C:
Column A Column B Column C
data Anim data Final data (the column where I need to fill in the data)
20 cat ???? (the lookup result should be monster)
34 mouse ???? (the lookup result should be 210)
999 fish ???? (the lookup result should be 8696)


The "????" in my worksheet "FILL-IN" is the data I'm looking for in the
"DATA-REF " worksheet. So meaning if I lookup "20" on Column A in worksheet
"DATA-REF" after it's found I want to lookup "cat" as per reference to Column
B on my worksheet "FILL-IN" then search the "cat" in "DATA-REF" corresponding
to "20" on Column A of my "DATA-REF" worksheet. So the final lookup result
should be "monster", as what I written there (the lookup result should be
monster).

Sorry if it sounds confusing but that is the best I can do. Pls help!

Thanks!