We help IT Professionals succeed at work.

find multiple conditions on different sheet

Euro5
Euro5 asked
on
I want to return a value if multiple conditions on a row are met.
When all conditions match on same row - the same date, region, etc.
It has to look up a whole list on a separate sheet and find the one line that has all these matches and return the 'code'.
If no line matches, return a blank.
Test.xlsx
Comment
Watch Question

You can try this Sumproduct formula:

=SUMPRODUCT(--(Sheet2!$A$2:$A$6=A2),--(Sheet2!$B$2:$B$6=B2),--(Sheet2!$C$2:$C$6=C2),Sheet2!$D$2:$D$6)

See attached.


PaulTest013020.xlsx

Analyst Assistant
Commented:

This array formula, entered with CTRL+SHIFT+ENTER, in D1 and copied down works for your example data.


=INDEX(Sheet2!D:D,MATCH(A2&B2&C2, Sheet1!A:A&Sheet2!B:B&Sheet2!C:C, 0))

Author

Commented:
Thank you  - this is quite valuable and was very quick!!