Using multiple IF statements with INDEX/MATCH
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
-
=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))
-
@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
-
=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))
-
@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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!