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
Best Answer

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

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 465 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!