Nested IF/INDEX/MATCH Challenge

Options

I am stumped and hoping someone can help.

Here is my sheet:

In the [Partner Name] column, I have this formula:

=IF(OR(BEGEOID@row >= 34, BEGEOID@row <= 94956), INDEX({BEGEOID Partner List A Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List A BEGEOID}, 0)), IF(OR(BEGEOID@row >= 94957, BEGEOID@row <= 431490), INDEX({BEGEOID Partner List B Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List B BEGEOID}, 0)), IF(OR(BEGEOID@row >= 431491, BEGEOID@row <= 550684), INDEX({BEGEOID Partner List C Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List C BEGEOID}, 0)), IF(OR(BEGEOID@row >= 550685, BEGEOID@row <= 617472), INDEX({BEGEOID Partner List D Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List D BEGEOID}, 0)), IF(OR(BEGEOID@row >= 617473, BEGEOID@row <= 659183), INDEX({BEGEOID Partner List E Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List E BEGEOID}, 0)), IF(OR(BEGEOID@row >= 659184, BEGEOID@row <= 688009), INDEX({BEGEOID Partner List F Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List F BEGEOID}, 0)), IF(OR(BEGEOID@row >= 688010, BEGEOID@row <= 710800), INDEX({BEGEOID Partner List G Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List G BEGEOID}, 0)), IF(BEGEOID@row >= 710801, INDEX({BEGEOID Partner List H Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List H BEGEOID}, 0)), "NO"))))))))

The range references you see here are to seven different sheets (BEGEOID Partner List A ... BEGEOID Partner List G). Each of those sheets contains <=10k rows.

What I'm trying to accomplish is to have Smartsheet look up the partner name based on the BEGEOID by referencing the correct sheet for the BEGEOID range. Syntactically, the formula is correct, but I should not be getting a #NO MATCH result for any of these values. Any idea why? If I've reached some kind of limit due to the size of the cross-referenced sheets, I didn't get an error message about that. So, I'm out of ideas.

Thanks in advance!

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!