Index Match 2 criteria to return Yes or No

emthaolinh

New Member
Joined
Apr 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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")
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,193
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
13,193
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=IF(COUNTIFS(Sheet2!E:E,E2,Sheet2!G:G,J1),"Yes","No")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,112
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 

Forum statistics

Threads
1,136,732
Messages
5,677,439
Members
419,693
Latest member
divtjd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top