Can an INDEX(MATCH) formula be built dynamically using @row data?
I am trying to build an INDEX(MATCH) formula in one column based on data in another column. I can work around this by using a huge IF statement but that exceeds the single cell capacity for a formula. Here is the basic formula:
INDEX({Cap - Week 1}, MATCH([CP-Key]@row, {Capacity - CP Key}, 0)
- The "Cap - Week 1" is the dynamic part, specifically the number 1, possible values that may exist in the other column are 1 - 52 (the week # of the year).
- How can I pull the @ROW value (1 - 52) and stick it into the INDEX formula so it looks like:
- INDEX({Cap - Week 1}, MATCH . . .
- INDEX({Cap - Week 2}, MATCH . . .
- INDEX({Cap - Week 3}, MATCH . . .
Thanks,
Andrew B. Boston
Best Answer
-
Hello again, just wanted to update you that your idea worked! Thanks very much. When reading the INDEX(MATCH()) description & samples, I wasn't picking up on how the optional [column_index] worked, I think more advanced examples would be greatly helpful.
Thanks again,
Andrew B. Boston
Answers
-
You can't, but what you can do is reference all 52 week columns in a single reference then use some sort of cell reference to indicate which column number to pull from.
=INDEX({Cap - All 52 Weeks}, MATCH([CP-Key]@row, {Capacity - CP Key}, 0), [Column Number]@row)
-
Hi Paul, thanks for the reply. It may work, but I need your help to better understand the solution. Are you suggesting that in "Manage References" I create a single relationship that includes the range of all 52 columns (all 52 weeks) and then tell the formula which column to pull back (like the VLOOKUP does)? Right now, I have 52 different relationships and refer to them separately. Please confirm.
Thanks very much.
Andrew B. Boston
-
Hello again, just wanted to update you that your idea worked! Thanks very much. When reading the INDEX(MATCH()) description & samples, I wasn't picking up on how the optional [column_index] worked, I think more advanced examples would be greatly helpful.
Thanks again,
Andrew B. Boston
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!