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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!