Index Match with If Statements

BFuller

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.

repair.PNG

visit.PNG

I'd be super grateful for some help with this.

Genevieve P.

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

BFuller

Thank you so much!!! I will try it!

BFuller

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?

Genevieve P.

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))

BFuller

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!!

Genevieve P.

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