• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Criteria Matches on different spreadsheet

Jessica Conrad

New Member
Hello.

I have 2 spreadsheets that I need to compare. I need a formula that looks for criteria 1 and criteria 2 on spreadsheet 1 and looks for the same combo on a second spreadsheet to let me know if it is duplicated.

Example- I have a customer PO that goes to multiple cities on 2 spreadsheets. I need to know not only if a matching PO is on the second spreadsheet, but if PO 1234 AND the city of Hanover is as well. So I have a bunch of formulas that can tell me if one of the criteria is on the second spreadsheet, but not both of them.

Thanks for your help!
 
Something like this:
=ISNUMBER(MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))

Confirm this formula as an array using Ctrl+Shift+Enter, not just Enter.

True = Match found
False = No match found
 
Something like this:
=ISNUMBER(MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))

Confirm this formula as an array using Ctrl+Shift+Enter, not just Enter.

True = Match found
False = No match found


This is PERFECT!! Thank you so much!! Is there anyway to add something at the end saying if true type in "duplicate" or something? If not, no worries this is just fantastic!!
 
Sure thing. We'll take what we have, and stick in an IF function:
=IF(ISNUMBER(MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0)),"Duplicate","")
 
Back
Top