It looks like you want
=IFERROR(IF(INDEX(Sheet1!A:A, MATCH(B1,Sheet1!B:B,0))=A1, "same", INDEX(Sheet1!A:A, MATCH(B1,Sheet1!B:B,0))), "no match")
Put that into
Sheet2!C1 and drag/fill down.
(If you have header row(s), adjust the row numbers accordingly.)
MATCH(B1,Sheet1!B:B,0) takes the company name
from the current row on Sheet2 (
and looks for it among the company names on Sheet1 (
This returns either an index (location) or an error.
INDEX(Sheet1!A:A, (the above)) fetches the ID number
from Sheet1 that corresponds to the index number returned by
MATCH returned an error, this will also.
- Then we compare the ID number from Sheet1
with the ID number from Sheet2 (
If they are equal, display
otherwise display the ID number from Sheet1.
MATCH returned an error,
then everything that follows from it is an error.
In that case, display
I put in the
no match strings
for clarity / illustration purposes.
If you don’t want them, and want to display blanks,
just replace them with empty strings (
- The names must match exactly.
- This does not detect names that are present on Sheet1 but not Sheet2
(like “Buy More” and “Slate Rock and Gravel Company”).