Array nested IF statement

[sammberg]

Rule for nested IF statements is 7 as seen below. Since most of the
results in my formulae are TEXT is it possible to put these in to an
array IF statement.


=IF(AND(B5="",C5=""),"",IF(B5="ATM","Petty Cash",IF(B5="BC","Bank
Charges",IF(OR(B5="INV"),"Advance",IF(B5="LW","Withdrawal",IF(OR(B5="TRF",B5="W"),"Transfer
Money or
Wages",IF(B5="CR",VLOOKUP(C5,Sales!$A$5:$D$1000,4,FALSE),VLOOKUP(C5,Purchases!$C$5:$D$1000,2,FALSE))))))))

 

[Roger Govier]

Hi
You are obviously already comfortable with Vlookup's.
Why not create a small 2 column Table with your types, ATM, BC, INV, LW,
TRF,W in one column, and your Detailed Description in the adjacent
column. Name this range Types.
Then amend your formula to

=IF(AND(B5="",C5=""),"",
IF(B5="CR",VLOOKUP(C5,Sales!$A$5:$D$1000,4,FALSE),
IF(B5="DR",VLOOKUP(C5,Purchases!$A$5:$D$1000,4,FALSE),
VLOOKUP(B5,Types,2,0))))

 

[JE McGimpsey]

I would instead put your conditions into a table

J K
1 ATM Petty Cash
2 BC Bank Charges
3 INV Advance
4 LW Withdrawal
5 TRF Transfer Money or Wages
6 W =K5
7 CR =IF(ISNA(MATCH(C5,Sales!$A:$A,FALSE)),
"No Match - Sales", VLOOKUP(C5,Sales!$A:$D,4,FALSE))

and use something like:

=IF(COUNTA(B5:C5)=0,"",IF(ISNA(MATCH(B5,J:J,FALSE)),
IF(ISNA(MATCH(C5,Purchases!$C:$C,FALSE)),"No Match - Purchases",
VLOOKUP(C5,Purchases!$C:$D,2,FALSE)),VLOOKUP(B5,J:K,2,FALSE)))