# Index Match 2 criteria to return Yes or No

#### emthaolinh

##### New Member
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
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
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
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

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
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
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.

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
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.

Replies
4
Views
60
Replies
22
Views
571
Replies
4
Views
226
Replies
1
Views
74
Replies
4
Views
48

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.

### Which adblocker are you using?

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

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