Match function for multiple criteria

JANET WEEKS

I need to match two criteria on two sheets to return a decision symbol.

screenshot from Sheet 1

Screenshot from Sheet 2

I need to match Warehouse Location & Locator ID and the Inventory Item number for a Yes Decision

No Match would yield a NO Decision.

Paul Newcome

Ah. Ok.

=IF(COUNTIFS({Source Sheet Location ID}, @cell = [Location ID]@row, {Source Sheet Inventory Item}, @cell = [Inventory Item]@row) > 0, "Yes", "No")

Paul Newcome

Try something like this...

=IFERROR(INDEX(COLLECT({Source Sheet Decision Column}, {Source Sheet Location Column}, [Location Column]@row, {Source Sheet Locator ID Column}, [Locator ID]@row, {Source Sheet Inventory Number Column}, [Inventory Number]@row), 1), "No")

JANET WEEKS

=IFERROR(COLLECT({EQW Form Sheet Inventory Item Number}, [Inventory Item Number (Lot)]@row, {EQW Form Sheet Locator ID}, [Warehouse Location]@row, 1), "No")

Sorry, I am new to collect function. But I need the Survey Status the be Yes if they criteria match and No if they don't.

Paul Newcome

Do you have the survey status in sheet 1 and just want to pull it over to sheet 2, or are you trying to say that if everything meets the criteria to generate a status on sheet 2?

Are you matching on 2 criteria or 3?

The formula in your last comment is missing the INDEX function. Try following the syntax of the formula I previously provided to include the location of the parenthesis and whatnot.

=IFERROR(INDEX(COLLECT({Source Sheet Decision Column}, {Source Sheet Location Column}, [Location Column]@row, {Source Sheet Locator ID Column}, [Locator ID]@row, {Source Sheet Inventory Number Column}, [Inventory Number]@row), 1), "No")

JANET WEEKS

The Survey status is Harvey ball in sheet 2 that I want to change based on matching the two criteria.

JANET WEEKS

@Paul Newcome

If the Location ID@ row matches the Source Sheet Location ID and the Inventory Item @ row matches the source sheet Inventory Item then a decision harvey ball of 'Yes"

If none of these match then harvey ball decision would be "No"

would be entered into the Survey status of sheet 2

Paul Newcome

Ah. Ok.

=IF(COUNTIFS({Source Sheet Location ID}, @cell = [Location ID]@row, {Source Sheet Inventory Item}, @cell = [Inventory Item]@row) > 0, "Yes", "No")

JANET WEEKS

@Paul Newcome

THANK YOU!!!!! It worked!!!! I never would have thought about COUNTIFS in this scenario.

Paul Newcome

Happy to help. 👍️