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))))

--
Regards

Roger Govier


"sammberg" <[email protected]> wrote
in message news:[email protected]...
>
> 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))))))))
>
>
> --
> sammberg
> ------------------------------------------------------------------------
> sammberg's Profile:
> http://www.excelforum.com/member.php...fo&userid=1019
> View this thread:
> http://www.excelforum.com/showthread...hreadid=520489
>

[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)))

In article <[email protected]>,
sammberg <[email protected]>
wrote:

> 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))))))))