Lookup question

[Lorderon]

Hi,
I have 2 worksheets "Sheet1" and "Sheet2"
"Sheet1" contains my raw data which is:
Col A Col B Col C
Dog 1 Adata
Cat 2 Bdata
Bird 3 Cdata
Dog 4 Ddata
Cat 5 Edata
Bird 6 Fdata
Dog 7 Gdata
Cat 8 Hdata
Bird 9 Idata


"Sheet2" contains my work data and I need to fill in Col C on this worksheet:
Col A Col B Col C
Dog 4 Ddata
Cat 5 Edata
Bird 6 ???
Dog 1 ???
Cat 8 ???
Bird 3 ???
Dog 7 ???
Cat 8 ???
Bird 6 ???


So, how to get the correct result (that is fill-in the "???" items)? I
already fill-in the first 2 items (expected result) on Col C, meaning it
needs to find the data
in Sheet1 that matches Col A and Col B on Sheet2, then copy the result in Col
C on Sheet2.

How? Pls help. Thanks!

 

[pub]

Hi,
I have 2 worksheets "Sheet1" and "Sheet2"
"Sheet1" contains my raw data which is:
Col A Col B Col C
Dog 1 Adata
Cat 2 Bdata
Bird 3 Cdata
Dog 4 Ddata
Cat 5 Edata
Bird 6 Fdata
Dog 7 Gdata
Cat 8 Hdata
Bird 9 Idata


"Sheet2" contains my work data and I need to fill in Col C on this
worksheet: Col A Col B Col C
Dog 4 Ddata
Cat 5 Edata
Bird 6 ???
Dog 1 ???
Cat 8 ???
Bird 3 ???
Dog 7 ???
Cat 8 ???
Bird 6 ???


So, how to get the correct result (that is fill-in the "???" items)? I
already fill-in the first 2 items (expected result) on Col C, meaning
it needs to find the data
in Sheet1 that matches Col A and Col B on Sheet2, then copy the result
in Col C on Sheet2.

How? Pls help. Thanks!

from the data given, it looks likeyou can do a vlookup() based on column
B only.
if your data starts in row 1 and ends row 9

=VLOOKUP(B1,Sheet1!$B$1:$C$9,2,FALSE)

then you should be able to put this in your c column and copy&paste down.

 

[Max]

One way is to use a multi-criteria index/match, array-entered

In Sheet2,
Paste this into C1's formula bar,
then press CTRL+SHIFT+ENTER to array-enter the formula:
=INDEX(Sheet1!C$1:C$9,MATCH(1,(Sheet1!A$1:A$9=A1)*(Sheet1!B$1:B$9=B1),0))
Copy C1 down. Adapt the ranges to suit the actual extents of your data.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Teethless mama]

=INDEX(Sheet1!$C$1:$C$9,MATCH(1,INDEX((Sheet1!$A$1:$A$9=A1)*(Sheet1!$B$1:$B$9=B1),),))

just press ENTER

 

[Lorderon]

Hi,
I tried this but error result "#N/A"....Then I tried the suggestion
"=INDEX(Sheet1!$C$1:$C$9,MATCH(1,INDEX((Sheet1!$A$1:$A$9=A1)*(Sheet1!$B$1:$B$9=B1),),))
" and it worked!

Thanks anyway...

 

[Max]

I tried this but error result "#N/A"....

You probably didn't array-enter the formula properly, although I took the
pain of highlighting this step explicitly in the response.

If you did the "press CTRL+SHIFT+ENTER" bit properly, the formula should
appear wrapped with curly braces within the formula bar, viz, it should look
like this:

{=INDEX(Sheet1!C$1:C$9,MATCH(1,(Sheet1!A$1:A$9=A1)*(Sheet1!B$1:B$9=B1),0))}

If you don't see the curlies, just re-click inside the formula bar,
re-do the CTRL+SHIFT+ENTER confirmation
Then look again that the curlies are there

If the formula is not array-entered, it will not return the correct result

Give it another try. I assure you that it works.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

 

[Rob]

I realize that this is a year old but I just now finally found it and IT IS
what I have been looking for for a very long time. THANK YOU VERY MUCH!!


P.S. I voted yes on the answer.