I have two worksheets (sheet 1 and sheet 2) Sheet 1 contains column A of numbers associated with Column B) - Think company company ID numbers in A, and Company names in B.

Sheet 2 has another group of Column A ID Numbers associated with column B company names in that Sheet) - This group used similar Company names, but used a different company ID numbering system

I want to compare sheet 1 to sheet 2. Where sheet 1 name matches a name in sheet 2, I want to add a new column so that I see the company ID number from Sheet 1.

I have tried several examples found, but I am not getting any results.

Thank you, Bob

  • 1
    Please show what you have tried. It gives us more insight into your problem – Scott Craner Nov 14 '17 at 20:51

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 (B1), and looks for it among the company names on Sheet1 (Sheet1!B:B).  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.  If MATCH returned an error, this will also.
  • Then we compare the ID number from Sheet1 with the ID number from Sheet2 (A1). If they are equal, display same, otherwise display the ID number from Sheet1. 
  • If MATCH returned an error, then everything that follows from it is an error.  In that case, display no match.

I put in the same and 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”).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.