Index Match help

[ScabbyDog]

I want a formula to look at what is in sheet2 column D and column E and if what is in col D is found in sheet1 col F and if what is found in sheet2 col E is found in sheet1 col D (both of these on the same row) then put YES into the cell where I enter the formula.

[BadlySpelledBuoy]

Hi ScabbyDog. Any chance of some sample data? Makes it much easier to provide a solution...

[ScabbyDog]

sample.xlsx

Here you go.
In this example if I enter the formula on sheet1 col H, I would want "YES" to be entered in row 1 and row 3

[BadlySpelledBuoy]

Try this in H1 on Sheet1 and copy down.

=IF(COUNTIFS(Sheet2!D:D,F1,Sheet2!E:E,D1)>0,"Yes","")

[ScabbyDog]

That works BadlySpelled - but is there an array formula that can be used as with lots of data this is slow to update when data changes.

[BadlySpelledBuoy]

Perhaps this will help. Seems to fit your needs and will explain the workings far better than I can.

http://www.ozgrid.com/Excel/count-if.htm

[ScabbyDog]

How would I edit =IF(COUNTIFS(Sheet2!D:D,F1,Sheet2!E:E,D1)>0,"Yes","") so that it also says Yes if another countif is >0.

Something like =IF(COUNTIFS(Sheet2!D:D,F1,Sheet2!E:E,D1)>0,"Yes","") OR =IF(COUNTIFS(Sheet2!Z:Z,F1,Sheet2!E:E,D1)>0,"Yes","")