# Index-Match Excel

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

• 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 (`""`).

### Example:

Sheet1:

Sheet2:

Notes:

• 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”).