Compare two lists for matches

[Whitney]

I would like to compare Sheet A (2005) with Sheet B (2007) I would like Sheet
A to reference Sheet B and look for a match for Consultant ID, if it finds
Consultant ID than to compare Tax ID and tell me if they match or not. I
need to know if it finds a match for consultant ID (Yes/No) and then if the
Tax ID matches (Yes/No). What type of match or lookup formula can I use?

Sheet A (2005)
A B C
1 Consultant ID Tax ID Tax ID Updated?
2 1234 456 No
3 1235 654 Yes
4 1236 659 Not Found

Sheet B (2007)
A B
1 Consultant ID Tax ID
2 1234 456
3 1235 657
4 1289 637

 

[Bob Phillips]

=IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),IF(ISNUMBER(MATCH('Sheet
A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Whitney]

Excellet, that worked!

Now my next question is what formula can I use for the rows that resulted in
No (not match for Tax ID) to then populate the new Tax ID in column D?

 

[Bob Phillips]

=IF(C2="No",VLOOKUP(A2,'Sheet B'!A:B,2,FALSE),"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)