Match function for multiple criteria
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.
Best Answer
-
Ah. Ok.
=IF(COUNTIFS({Source Sheet Location ID}, @cell = [Location ID]@row, {Source Sheet Inventory Item}, @cell = [Inventory Item]@row) > 0, "Yes", "No")
Answers
-
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")
-
=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.
-
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")
-
The Survey status is Harvey ball in sheet 2 that I want to change based on matching the two criteria.
-
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
-
Ah. Ok.
=IF(COUNTIFS({Source Sheet Location ID}, @cell = [Location ID]@row, {Source Sheet Inventory Item}, @cell = [Inventory Item]@row) > 0, "Yes", "No")
-
THANK YOU!!!!! It worked!!!! I never would have thought about COUNTIFS in this scenario.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!