Index Match returning blanks when there is a Match?

Hello,

I have a nested IFERROR Index/Match that is returning a Market Code based on a Unique ID. Formula looks more complicated than it is:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({CA}, MATCH([MPL #]@row, {CA}, 0), 2), INDEX({DE}, MATCH([MPL #]@row, {DE}, 0), 2)), INDEX({GA}, MATCH([MPL #]@row, {GA}, 0), 2)), INDEX({MA}, MATCH([MPL #]@row, {MA}, 0), 2)), INDEX({MD}, MATCH([MPL #]@row, {MD}, 0), 2)), INDEX({ME}, MATCH([MPL #]@row, {ME}, 0), 2)), INDEX({MI}, MATCH([MPL #]@row, {MI}, 0), 2)), INDEX({MS}, MATCH([MPL #]@row, {MS}, 0), 2)), INDEX({NJ}, MATCH([MPL #]@row, {NJ}, 0), 2)), INDEX({NM}, MATCH([MPL #]@row, {NM}, 0), 2)), INDEX({NY}, MATCH([MPL #]@row, {NY}, 0), 2)), INDEX({OH}, MATCH([MPL #]@row, {OH}, 0), 2)), INDEX({PA}, MATCH([MPL #]@row, {PA}, 0), 2)), INDEX({RI}, MATCH([MPL #]@row, {RI}, 0), 2)), INDEX({VA}, MATCH([MPL #]@row, {VA}, 0), 2)), INDEX({WI}, MATCH([MPL #]@row, {WI}, 0), 2))

MPL # is the matching number and the value i am trying to capture is a state code that gets generated on a separate sheet. It is working correctly for about 85% of the rows.

I am receiving a few #INVALIDVALUE for a specific State, which makes me think the State Code or MPL # are out of range of the reference I created but they are not.

More often however, I am seeing just a blank cell in the State Code column, even when there is a corresponding MPL # so the formula should be working. I am seeing this in States where it is working most of the time but a few here and there are not carrying over the state code despite having a corresponding MPL #.

Any guidance would be appreciated

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Larry

@Jeff Reisman

There is one less parentheses in the beginning because it is 'opening up" the first IFERROR option.

I did however fix both errors. I was using one reference for each state because the columns were adjacent. I added a {State1} that referenced the State/Market Code for the Index and {State2} that referenced the MPL # for the Match.

Simplified version looks like this:

INDEX({State1}, MATCH([MPL #]@row, {State2}, 0)))

I have the nested IFERROR simply so I can parse through each State/Market sheet.

All comments

Jeff Reisman

@Larry

I've never seen a nested IFERROR... wow.

I did notice that you're missing an end parentheses at the end of your very first INDEX/MATCH. No idea if that's causing your issue, just something I noticed.

Larry

@Jeff Reisman

There is one less parentheses in the beginning because it is 'opening up" the first IFERROR option.

Simplified version looks like this:

INDEX({State1}, MATCH([MPL #]@row, {State2}, 0)))

I have the nested IFERROR simply so I can parse through each State/Market sheet.

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.