Multiple Criteria Matching from Two Sheets

[brishtee]

Hi Everyone,

I am sure this question was asked before and answered as well. But I couldn't find an appropriate response after much search - so if anyone can help with this (or direct me to old thread with a response), it would be much appreciated.

I have two data sheets with multiple rows each: Sheet 1 & Sheet 2. The data is under the following categories. I need to check all 4 categories in Sheet 1 with Sheet 2 data, and return on Sheet 1 - "match" or "no match".

NAME - LAST NAME - DATE OF BIRTH - PHONE

Thank you very much
Brishtee

[Aardigspook]

You can look up the concatenated (joined together) values for each row on Sheet 1 against the table on Sheet 2, also concatenated - if this produces an error, there is no match.
If your data is in columns A to D on each sheet, starting on row 2, then put this in E2 then drag down as far as you need:

Please Login or Register  to view this content.

Amend the ranges to match your data - not just the column letters, but the row numbers - the formula above looks just at rows 2 to 20 on sheet 2.
Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result (in this case, you'll get 'no match' for every row). If that happens, just click into the formula bar and try again.

I've attached a file showing this working.

Hope that helps.

[brishtee]

This is awesome!! Thank you so much. Learned a wonderful formula, to use for other future projects as well.

[Aardigspook]

You're welcome, glad I could help and thanks for the rep.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.