Using multiple IF statements with INDEX/MATCH

Kahlan
Kahlan ✭✭
edited 10/24/22 in Formulas and Functions

I am using a value posted in a column of my sheet using other formulas; say that value = L4, L5, or L6 (levels of approvers), search a reference sheet for a specific related column and return the email for the person.

I have a more basic function working with standard Index/Match function but adding in the element of if specific column = L4, L5, or L6 keeps coming up unparseable.

Current failing formula:

=IF([Finance Approver Level Hidden Fx]@row, "L4", INDEX({Finance/Legal Matrix Range 1},MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}), 0)) IF([Finance Approver Level Hidden Fx]@row, "L5", INDEX({Finance/Legal Matrix Range 2}, MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}), 0)) IF([Finance Approver Level Hidden Fx]@row, "L6", INDEX({Finance/Legal Matrix Range 3}, MATCH([Requester Business Unit]@row),{Finance/Legal Matrix Range 4}, 0))


IF Finance Approver Level Hidden Fx]@row = L4 then search {Finance/Legal Matrix Range 1} for Business Unit from current sheet

If Finance Approver Level Hidden Fx]@row = L5 then search {Finance/Legal Matrix Range 2} for Business Unit from current sheet

If Finance Approver Level Hidden Fx]@row = L6 then search {Finance/Legal Matrix Range 3} for Business Unit from current sheet

Best Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Kahlan

    =IF([Finance Approver Level Hidden Fx]@row = "L4", INDEX({Finance/Legal Matrix Range 1},MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L5", INDEX({Finance/Legal Matrix Range 2}, MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L6", INDEX({Finance/Legal Matrix Range 3}, MATCH([Requester Business Unit]@row),{Finance/Legal Matrix Range 4}, 0))

  • Kahlan
    Kahlan ✭✭
    Answer ✓

    @Mike TV That was close enough to get me thinking correctly! Thank you!

    Here's the full scope of what it was after I got it fully operational:

    =IF([Finance Approver Level Hidden Fx]@row = "L4", INDEX({Finance/Legal Matrix Range 5}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L5", INDEX({Finance/Legal Matrix Range 2}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L6", INDEX({Finance/Legal Matrix Range 1}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L3", INDEX({Finance/Legal Matrix Range 3}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0))))))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Kahlan

    =IF([Finance Approver Level Hidden Fx]@row = "L4", INDEX({Finance/Legal Matrix Range 1},MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L5", INDEX({Finance/Legal Matrix Range 2}, MATCH([Requester Business Unit]@row,{Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L6", INDEX({Finance/Legal Matrix Range 3}, MATCH([Requester Business Unit]@row),{Finance/Legal Matrix Range 4}, 0))

  • Kahlan
    Kahlan ✭✭
    Answer ✓

    @Mike TV That was close enough to get me thinking correctly! Thank you!

    Here's the full scope of what it was after I got it fully operational:

    =IF([Finance Approver Level Hidden Fx]@row = "L4", INDEX({Finance/Legal Matrix Range 5}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L5", INDEX({Finance/Legal Matrix Range 2}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L6", INDEX({Finance/Legal Matrix Range 1}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0)), IF([Finance Approver Level Hidden Fx]@row = "L3", INDEX({Finance/Legal Matrix Range 3}, MATCH([Requester Business Unit]@row, {Finance/Legal Matrix Range 4}, 0))))))

  • Hello, I have a similar formula that is throwing a UNPARSEABLE error:

    =IF(INDEX({Coach Log %}, MATCH(District@row, {District}, 0)) < 0.8, "Low"), IF(INDEX({Coach Log %}, MATCH(District@row, {District}, 0)) < 0.5, "High"))

    The first IF statement works but when I add the second statement, it throws the error.

  • Hi @Summer Edwards

    Were you able to get this resolved?

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

  • @Genevieve P. I ended up going in a bit different direction with the sheet and what I wanted to display but here's the one I ended up with:

    =IFERROR(IF(INDEX(COLLECT({Date}, {Coach/District}, $[District/Coach]@row, {Date}, >=DATE(2022, 10, 1), {Date}, <=DATE(2022, 10, 31)), 1) <> "", "Yes"), "No")

    Allows me to indicate Yes or No in a month column to indicate a log was completed for that month for a specific district/coach. Works great.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!