Expanding Nested IF

[lisa6538]

I have a nested IF statement that I need to expand if a new column is added which happens often. Wondering if there is a better way to handle this?

Here's my formula
=IF($A$4=$D$4,$D7,IF($A$4=$E$4,$E7,IF($A$4=$F$4,$F7)))

If works perfectly, except I will be adding a column G next month and H the following month, etc. I tried to nest an OR in the IF statement which works for the logical test, but not for the value if true.

Any suggestions would be appreciated.

Thank you!

 

[RickXL]

If I am understanding this correctly, the MATCH part only needs to return a column number based on the contents of row 4, so this should work without Ctrl + Shift + Enter:

=INDEX($D7:$ZZ7,MATCH($D$4,$D$4:$ZZ$4,0))

I use look up columns from D to ZZ.
As the formula is dragged down the row to be looked up and copied to column B will vary.
I note, this substitutes zeroes for blanks as well.


Regards,

 

[RickXL]

I note, this substitutes zeroes for blanks as well.

OK, I unchecked the option to: Show a zero in cells that have zero value

 

[sheetspread]

Conditional and custom formatting do that too, as well as an if then "" in the formula (sometimes iferror can be substituted for a shorter formula but maybe not here):

Three ways to hide zero values in an Excel sheet - TechRepublic

=HLOOKUP($A$4,$D$4:$ZZ7,ROW(B7)-3,0)

seems like the best choice

 

[FlameRetired]

Try =INDEX($D7:$ZZ7&"",MATCH($D$4,$D$4:$ZZ$4,0))

It should suppress 0s in blank cells.

 

[RickXL]

Try =INDEX($D7:$ZZ7&"",MATCH($D$4,$D$4:$ZZ$4,0))

It should suppress 0s in blank cells.

Thanks for the tip - that works, too.

I must remember to not change options if I am not sure what they do!

Regards,

 

[FlameRetired]

You're welcome. It's always good to have alternatives.