INDEX, MATCH, AND formula
I am trying to create an Index / Match cross-sheet formula if two cross-sheet criteria are met.
Sheet One displays the Sales Reps for New and Modified Items:
I want to display the Item Rep for NEW item types.
The formula entered on Sheet Two is:
=IF(AND(MATCH(Item1, {SHEET ONE_ITEM}, 0), MATCH("NEW",
{SHEET ONE_ITEM STATUS}, 0)), INDEX({SHEET ONE_SALES
REP}, MATCH(Item1, {SHEET ONE_ITEM}, 0)))
It appeared to be working for Item One but when I copy and paste the formula, it gives different results. Also, if I move the Item Order on Sheet One, it displays different data. Same formula, I just moved the item order. I assume that my formula is only looking for the first Item ONE that matches NEW in the First Row on Sheet One.
Any ideas on how I can display Sheet One NEW item type Sales Rep on Sheet Two? A simple Index/Match formula will not work, I need two criteria to match before the data is indexed. The report should look like:
Thank you for your help.
Comments
-
Screen shots attached.
-
Instead of an INDEX/MATCH, try using a JOIN/COLLECT.
=JOIN(COLLECT({SHEET ONE_SALES REP}, {SHEET ONE_ITEM}, Item1, {SHEET ONE_ITEM STATUS}, "NEW"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
It worked, thank you so much for your help!
Emily
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives