# Index Match with If Statements

Options
✭✭✭
edited 06/28/22

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.

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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

• ✭✭✭
Options

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?

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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

• Employee
Options

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