Nested IF statement with 18 IFs

[LarryC_SA]

I am trying to figure a better method than using a Nested IF statement with 18 IFs. The below example is only 3 IFs, but my data has 18 columns of amounts. I have to assign an Account_No based on if there is a value in an Amount column. I couldn't figure out how to use VLOOKUP for this. Not sure if INDEX and MATCH will work.

The formula for AccountNo field is:
=IF(B2<>0,"12345",IF(C2<>0,23456,IF(D2<>0,34567,99999)))

I am using Excel 2013 so IFS won't work.

Any help would be appreciated. Thanks.


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Sales
[/TD]
[TD]COGS
[/TD]
[TD]Interest
[/TD]
[TD]GLAccount_No
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]$5000
[/TD]
[TD]$0
[/TD]
[TD]$00
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]$0
[/TD]
[TD]$600
[/TD]
[TD]$0
[/TD]
[TD]23456
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$250
[/TD]
[TD]34567
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]99999
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]$0
[/TD]
[TD]$75
[/TD]
[TD]$0
[/TD]
[TD]23456
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Are you just looking for the first non zero in column B, and returning corresponding value from column E ?

Try

=INDEX(E2:E6,MATCH(1,INDEX(B2:B6>0,0),0))

Or if there will only be 1 value > 0 in the column (otherwise it would actually return the last non 0)
=LOOKUP(2,1/(B2:B6>0),E2:E6)

 

[mrhstn]

You can do this with an array index/match combo. Ideally you would have a row/column with the return values. For your above 3 IF example you can use

=INDEX({12345,23456,99999}, MATCH(TRUE, $B12:$D12<>0, 0))

This needs entered using keystroke ctrl+shift+enter, not just enter.

Which will return the first position where the columns don't equal 0

 

[LarryC_SA]

Jonmo1, there is no value in column E. I am trying to populate it with a G/L account number based on if an Amount column has $$$ <> 0.

mrhstn, thank you. This worked, but I tweaked it a little so I wouldn't have to include all the AccountNo values in the INDEX. I created a 2 column table (tblGL_Values). Column 1 was 1,2,3,4.... 18. Column 2 was the associated values to those 1-18 numbers. It allows me to maintain a table of the columns so if I add or remove any, I don't need to change the formula. The MATCH portion returns the column # and I use that for the VLOOKUP.

Also, thanks for the tip on having the formula as an array.

=VLOOKUP(MATCH(TRUE,$B4:$D4<>0,0),tblGL_Values,2,FALSE)

P.S. As an aside, the original data has multiple Amount fields on the same record with amounts. I had a DBA "split" records so only 1 Amount field was populated. I didn't want to try that considering 1 month's of data had over 45K records, and I wasn't going to figure out how to do that in Excel/VBA for 2 years worth of monthly data. Excel may have choked on the "splitting".