Can an INDEX(MATCH) formula be built dynamically using @row data?

Andrew B. Boston

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

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

Paul Newcome

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)

Andrew B. Boston

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

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