Compare 2 lists in 2 spread sheets

Hi All

Spread sheet 1

Contains a column with a list of ID numbers (all are 4 numbers some are 4 numbers with a letter on the end) and colours to go with the ID number in next the column. Approx. 50 lines

Spread sheet 2

This is a spread sheet with multiple columns but have the same 2 has spread sheet 1 - The list in spread sheet can change every time I run the report (Always the same headings)

What I would like is to run spread sheet 2 to get list of data

Then compare the list in in spread sheet 1 to see if any of the data appears in spread sheet 2. if yes then in some way tell me which.

Thank you for reading this

it can be done with index / match.

1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Main list

a2=C2&D2&E2&F2 and drag down



List to be checked

t5=A5&E5&B5&D5 and drag down

u5=iferror(if(match(T5,'Main List'!$A$2:$A$29,0)>0,"match","")"") and drag down.

The formula are in the yellow en green cells.

The result is in thg green cells.

Thanks for the rep.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.