Compare two lists for matches
 

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

Compare two lists for matches
 

=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" wrote in message
...
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

Compare two lists for matches
 

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" wrote:

=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" wrote in message
...
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

Compare two lists for matches
 

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



"Whitney" wrote in message
...
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" wrote:

=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" wrote in message
...
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