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

Find more posts tagged with

Accepted answers

Mike TV

@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

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

All comments

Mike TV

@Kahlan

Kahlan

@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:

Summer Edwards

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.

Genevieve P.

Hi @Summer Edwards

Were you able to get this resolved?

Summer Edwards

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.