Nested IF/INDEX/MATCH Challenge

Danielle Arteaga

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!

Danielle Arteaga

WELP. Solved my own problem.

I needed "AND" instead of "OR" as the condition. Oops. :-)

Danielle Arteaga

WELP. Solved my own problem.

I needed "AND" instead of "OR" as the condition. Oops. :-)