Nested IF/INDEX/MATCH Challenge
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!
Best Answer
-
WELP. Solved my own problem.
I needed "AND" instead of "OR" as the condition. Oops. :-)
Answers
-
WELP. Solved my own problem.
I needed "AND" instead of "OR" as the condition. Oops. :-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!