Index Match 2 criteria to return Yes or No

[emthaolinh]

Please assist. I have 2 spreadsheets that I need to compare the values. The formula looks for the 2 fields in spreadsheet 1 in spreadsheet 2 and if it matches returns "YES" OR "NO".

IFERROR(IF(MATCH(sheet1!$E2,sheet2!$E$2:$E$5873,0)=MATCH(sheet1!$J2,sheet2!$A$2:$G$5873,0),INDEX(sheet2!$A2:$H5873,MATCH(sheet1!$E2,sheet2!$E2:$E5873,0),3),""),"YES","NO")

 

[jasonb75]

Where are you looking for a match for the value in J2 of sheet1?

Your formula shows 7 columns but a match range can only be 1 column (or 1 row).

Remember, a formula that doesn't do what you want doesn't tell us what you want to do, it only shows us your unsuccessful attempt.

 

[emthaolinh]

It's actually 2 columns (fields) E2 and J2 values in spreadsheet 1 that I need to match in spreadsheet 2. This is a customer pricing file. E2 (unique product ID) and J2 (price). If these two fields matches in spreadsheet 2, then the formula will return "YES" or "NO" so I know that the pricing file upload was done properly. Thank you for your assistance.

 

[emthaolinh]

spreadsheet 1 - E2 (unique ID) must match spreadsheet 2 range of e2:E5873.
spreadsheet 1 - J2 (price) must match spreadsheet 2 range of G2:G5873.

If the two criterias above matches then it returns "YES" or NO.

Thanks,

 

[emthaolinh]

Where are you looking for a match for the value in J2 of sheet1?

Your formula shows 7 columns but a match range can only be 1 column (or 1 row).

Remember, a formula that doesn't do what you want doesn't tell us what you want to do, it only shows us your unsuccessful attempt.

I posted on the thread. Can you review and assist? Thanks so much.

 

[jasonb75]

That is what I thought you were trying to do but your formula was so mixed up that it was impossible to make any sense out of it.

=IF(COUNTIFS(Sheet2!E:E,E2,Sheet2!G:G,J1),"Yes","No")

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: index match using multiple spreadsheets return text YES OR NO - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[emthaolinh]

That is what I thought you were trying to do but your formula was so mixed up that it was impossible to make any sense out of it.

=IF(COUNTIFS(Sheet2!E:E,E2,Sheet2!G:G,J1),"Yes","No")

Thanks, so much! I manipulated the name of the sheets to match mine and it works.