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!

 

[sheetspread]

Can't you use hlookup instead?

 

[Tetra201]

If the pattern remains the same, here is a formula. It should be entered using Ctrl+Shift+Enter, not just Enter.

=INDEX($D$7:$H$7,MATCH(1,--($D$4:$H$4=$A$4),0))

 

[CyrusTheVirus]

Hi Lisa,

Welcome to the forums.

Can you please post a sample of your data that way we can see what's going on so we know exactly what it is that you are trying to achieve?

Also, how many columns are you planning on adding, will there be an end to it?

Or maybe what sheetspread was saying...

=HLOOKUP(A4,$D$4:$XFD$7,4,0)

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

This seems to work: =HLOOKUP(A4,D4:H7,4,0)

It performs a horizontal look up matching A4 with the data in D4:H7. If it finds an exact match it returns the value from the 4th row in the Range i.e. row 7.
Instead of H7 you could specify the end of the Range much farther out e.g. ZZ7. It depends on how far you want to go.

If you want to return FALSE like the nested IFs when the value is not found you could use: =IFERROR(HLOOKUP(A4,D4:ZZ7,4,0),FALSE)



Regards,

 

[lisa6538]

Using Excel 2013. The data in columns D-L will change on rows 7-21. There could be more columns based on how many months and years we are reporting on. Here's my spreadsheet. The IF formula is in Column B =IF($A$4=$D$4,$D7,IF($A$4=$E$4,$E7,IF($A$4=$F$4,$F7))). Everything else is static data.



[TABLE="width: 768"]
<tbody>[TR]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD="align: right"]2016
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[TD="align: right"]2016
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]2025
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2025
[/TD]
[TD="align: right"]2024
[/TD]
[TD="align: right"]2023
[/TD]
[TD="align: right"]2022
[/TD]
[TD="align: right"]2021
[/TD]
[TD="align: right"]2020
[/TD]
[TD="align: right"]2019
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]2017
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Footnote Formula
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 7
[/TD]
[TD]a
[/TD]
[TD][/TD]
[TD]a
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 10
[/TD]
[TD]b
[/TD]
[TD][/TD]
[TD]b
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 12
[/TD]
[TD]c
[/TD]
[TD][/TD]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 17
[/TD]
[TD]d
[/TD]
[TD][/TD]
[TD]d
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line 21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[lisa6538]

I tried the Index Match with the array that Tetra101 suggested and it works. I can't figure out the HLookup though.

 

[sheetspread]

I can't test until later, but doesn't =INDEX($D$7:$H$7,MATCH($A$4,$D$4:$H$4,0)) without ctrl-shift-enter or the hlookups that Cyrus and Rick posted have the same effect?

 

[RickXL]

Now I have seen the data, I think the INDEX/MATCH solution will be better.

It looks as if the formula is to be placed in cell B7 and then dragged down.
The HLOOKUP formula would be something like: =HLOOKUP($A$4,$D$4:$ZZ7,ROW(B7)-3,0)

However, that inserts a zero where empty cells are looked up. A workaround will be required for that.


Regards,

 

[Tetra201]

... doesn't =INDEX($D$7:$H$7,MATCH($A$4,$D$4:$H$4,0)) without ctrl-shift-enter or the hlookups that Cyrus and Rick posted have the same effect?

They have to.