Index Match with If Statements

Options
BFuller
BFuller ✭✭✭
edited 06/28/22 in Formulas and Functions

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

  • Genevieve P.
    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • BFuller
    BFuller ✭✭✭
    Options

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

  • BFuller
    BFuller ✭✭✭
    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?

  • Genevieve P.
    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))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • BFuller
    BFuller ✭✭✭
    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!!

  • Genevieve P.
    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!