Excel Dynamic Column Reference in Formula

Question

I want to update the cell reference in C3 depending on the 'Current Day'.

For example, if 'Current Day' is 'Thu' then I'd like C3 to be 'Z3'

Any suggestions to get me started? webpages to read?

Answer 1

To get the value on the basis of Current Day use the following formula:

=HLOOKUP(H3,$I$2:$O$3,2,FALSE)

See image for reference

On seeing the formula in image attached probably you are looking for INDIRECT function:

=INDIRECT("'5+ 000""s By Day'!" & C3)

You can combine both in one formula as

=INDIRECT("'5+ 000""s By Day'!" & HLOOKUP(H3,$I$2:$O$3,2,FALSE))

For details on INDIRECT function see this.

Answer 2

A hlookup() would do it - =HLOOKUP(TEXT(TODAY(),"DDD"),I2:O3,2,0)