If Row Contains Value in Cell, Return Column Number

[samcritchlow]

Hi, very first post here, hoping someone can help.

I'm trying to devise a formula for a number of cells in column C that returns TRUE IF the column matching the current week number (stored in B2) on the same row as the formula is not blank.

Hopefully the image below helps with the description above

Many Thanks!
Sam

 

[tyija1995]

Hey, could you try in C6 (then drag formula down):

IF(INDEX($E$3:$I$9,ROWS($C$3:$C6),MATCH($B$2,$E$3:$I$3,0))=0,FALSE(),TRUE())

 

[samcritchlow]

THAT was amazing! Thank you SO much!! ?

 

[tyija1995]

You're welcome, though it might not behave how you'd like if you input 0 into the cell (e.g. put 0 in cell G6), if this is the case you can try this other formula in C6:

IF(SUMPRODUCT(($B$2=$E$3:$I$3)*(NOT(ISBLANK($E6:$I6))))=1,TRUE(),FALSE())

 

[samcritchlow]

You're welcome, though it might not behave how you'd like if you input 0 into the cell (e.g. put 0 in cell G6), if this is the case you can try this other formula in C6:

IF(SUMPRODUCT(($B$2=$E$3:$I$3)*(NOT(ISBLANK($E6:$I6))))=1,TRUE(),FALSE())

Thanks for this follow-up. When working it into my actual sheet I did modify to use ISBLANK and it worked a treat!

 

[samcritchlow]

One additional question (out of curiosity more than anything):

Is there a way to make the lookup array span across an infinite amount of columns from the start point?

 

[tyija1995]

Sure, in the SUMPRODUCT formula just change the upper bound column in parallel with changing the ISBLANK upper bound column, the one provided gives upper bounds of column I.

If you are to change one you must also change the other to span an equal amount of columns otherwise you will return #N/A error.

So for example;

IF(SUMPRODUCT(($B$2=$E$3:$I$3)*(NOT(ISBLANK($E6:$I6))))=1,TRUE(),FALSE())

Becomes:

IF(SUMPRODUCT(($B$2=$E$3:$XFD$3)*(NOT(ISBLANK($E6:$XFD6))))=1,TRUE(),FALSE())

Where XFD is the final column you can have on a spreadsheet. (Excel spreadsheets contains 2^14 columns = 16384 currently & (2^20 rows (1048576) if curious about rows)

 

[samcritchlow]

Sure, in the SUMPRODUCT formula just change the upper bound column in parallel with changing the ISBLANK upper bound column, the one provided gives upper bounds of column I.

If you are to change one you must also change the other to span an equal amount of columns otherwise you will return #N/A error.

So for example;

IF(SUMPRODUCT(($B$2=$E$3:$I$3)*(NOT(ISBLANK($E6:$I6))))=1,TRUE(),FALSE())

Becomes:

IF(SUMPRODUCT(($B$2=$E$3:$XFD$3)*(NOT(ISBLANK($E6:$XFD6))))=1,TRUE(),FALSE())

Where XFD is the final column you can have on a spreadsheet. (Excel spreadsheets contains 2^14 columns = 16384 currently & (2^20 rows (1048576) if curious about rows)

Great, thanks again