How to create dynamic column for MATCH function

[sagor]

I have a function that looks up a person’s rating from a column of numbers. The column number increments for each week, so for first week the formula reads:
INDEX($‘MVP Ranking’.$A:$A, MATCH($N6, $‘MVP Ranking’.$C:$C, 0))
$A:$A is the column with names, N6 is a number for rating, $C:$C is a rank list column
However, for the second week, the MATCH column is to be $D:$D, third week it should be $E:$E and so on.
This selection of $C:$C, etc is created in a separate cell “U1”. Using U1 in place of $C:$C does not work either
I’ve tried variations of INDIRECT but no success.

Any suggestions?
Thanks

[sagor]

I may have found a solution. The formula in the equation is not exactly what should be written. The MATCH should read:
MATCH($N7, INDIRECT(U2), 0)
where U2 is built from a string of values
“‘MVP Ranking’!”&U1
and U1 is built from the week number + 65

It boils down to the formula in the cell is not formatted like it should be, possibly a remnant from an Excel sheet it was imported from. Get rid of the period and replace with exclamation mark…

[sagor]

Final formula that does work is:
INDEX($‘MVP Ranking’.$A:$A,MATCH($M6,INDIRECT($T$2),0),0)
where cell T2 is a string built up from week number to produce the column value (“Y” in this case):
’MVP Ranking’!$Y:$Y

What is confusing is why some formulas have construct like the $‘MVP Ranking’. but the one that works is without the “$” and instead of a period, exclamation mark is used - ’MVP Ranking’!. First one produces the error, the second construct works with INDIRECT.

[keme1]

@sagor

It boils down to the formula in the cell is not formatted like it should be, possibly a remnant from an Excel sheet it was imported from. Get rid of the period and replace with exclamation mark…

Yes, there is a portability issue with differing address syntax, which becomes a problem when you “hardcode” address strings for use with INDIRECT(). If you use the INDIRECT function, build the address string using the ADDRESS() function instead of simple concatenation.

Your formula will easily become convoluted if you also need SUBSTITUTE()/REPLACE() to manipulate the address string for input into INDIRECT(). Consider using OFFSET() instead.

[keme1]

I’d use the OFFSET() function here. Something like …

... MATCH( $N6 ; OFFSET( $‘MVP Ranking’.$B:$B ; 0 ; <weeknumber> ) ; 0) ...

… assuming that your numbering for the weeknumber starts at 1 for the first week.

Replace <weeknumber> with a reference to a cell, or an inline calculation formula, returning the week number you want to use.

Side note
You may want to consider what to do with tied results. If you can have tied ranks, that will introduce ambiguity which may be detrimental to your result.

[sagor]

I don’t use “RANK”, I use a method of COUNTIFS in the column to produce a ranking. Yes, “RANK” sometimes produces missing or incorrect values when there is a tie.

[keme1]

@sagor

I don’t use “RANK”, I use a method of COUNTIFS in the column to produce a ranking. Yes, “RANK” sometimes produces missing or incorrect values when there is a tie.

Good! Was not intending to sound condescending in my “side note”. It is just a recurring issue, frequently overlooked, so I thought I’d mention it. Good to know that you got it in check.