Index Match with If Statements
Hello,
I have a formula question. I have external employees who do customer visits and repair visits. For every visit they enter a visit detail and when the primary reason for the visit is Repair/Troubleshoot, they have to do a repair detail. To keep them from having to reenter shared info, I did an index match formula utilizing a manual case ID. When they enter the manual case id on the repair side of things, the shared data populates the field.
It works great, but I want to close the loop and automatically detect if they select repair/troubleshoot but don't do a repair detail. My approach is to have the automatic case id on the Repair sheet to automatically populate a column on the Visit sheet when the primary reason for the visit for both sheets is Repair/Troubleshoot. I think I need an IF - INDEX - MATCH formula, but I am having the hardest time figuring it out.
I'd be super grateful for some help with this.
Answers
-
Hi @BFuller
What I would do in this case is first have an IF statement to check if the Primary Reason in the current sheet for that row is "Repair/Troubleshoot". If it is, then you can do an INDEX(COLLECT formula to find the Repair Form ID with two criteria (matching Case ID and matching Reason of "Repair/Troubleshoot"). See: Formula combinations for cross sheet references
Try something like this:
=IF([Primary Reason for Visit]@row = "Repair/Troubleshoot", INDEX(COLLECT({Repair Form ID}, {Case ID}, [Manual Case ID]@row, {Primary Reason}, [Primary Reason for Visit]@row), 1))
This should return a blank cell for any of your rows that don't have "Repair/Troubleshoot" in the current sheet.
Let me know if this makes sense and works for what you're trying to achieve!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much!!! I will try it!
-
I get Incorrect Argument Set. Here's my formula:
=IF([Primary Reason for Visit]@row = "Repair/Troubleshoot", INDEX(COLLECT({Repair Form ID}, {Visit Detail Report # on RD}, [Manual Case ID]@row, {Primary Reason for Visit RD}, [Primary Reason for Visit]@row, 1)))
Any idea where I went wrong?
-
Hi @BFuller
My apologies! I misplaced a closing parentheses at the end there. 🤦♀️
The 1 is for the INDEX function, to get the first match, not for COLLECT.
=IF([Primary Reason for Visit]@row = "Repair/Troubleshoot", INDEX(COLLECT({Repair Form ID}, {Visit Detail Report # on RD}, [Manual Case ID]@row, {Primary Reason for Visit RD}, [Primary Reason for Visit]@row), 1))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That seemed to work!!! Thank you!!! I am getting blank responses when the primary reason for visit doesn't match, the Repair form ID when it does match and #INVALID VALUE when the primary reason for visit is Repair/Troubleshoot, but it is not on the Repair Detail page. This is excellent!!! Thank you so much!!
-
No problem! I'm glad we were able to get this working for you 🙂
As a final tip, if you'd rather have a different message than INVALID VALUE, you can wrap an IFERROR statement around the whole thing, for example:
=IFERROR(IF([Primary Reason for Visit]@row = "Repair/Troubleshoot", INDEX(COLLECT({Repair Form ID}, {Visit Detail Report # on RD}, [Manual Case ID]@row, {Primary Reason for Visit RD}, [Primary Reason for Visit]@row), 1)), "Not Recorded")
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!