20 level nested If

[Guest]

Hi ...

I have a problem, and I would be very thankful if someone can help...

In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column
A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is
for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile
customer's account, and apply credits",... "action 20"). I have 20 'Actions'
to which I need to assign 20 'Action Codes'. What I need to do is this:
whenever I enter the 'Action Code' in any cell in 'column A', the
corresponding 'Action' that I have assigned to that 'Action Code' will
automatically appear in the adjacent cell in 'Column B'.

For simplification, let's assume that these are the 'action codes' and the
corresponding 'actions':


Action Code Action
1001 'action 1'
1002 'action 2'
1003 'action 3'
1004 'action 4'
1005 'action 5'
1006 'action 6'
1007 'action 7'
1008 'action 8'
1009 'action 9'
1010 'action 10'
1011 'action 11'
1012 'action 12'
1013 'action 13'
1014 'action 14'
1015 'action 15'
1016 'action 16'
1017 'action 17'
1018 'action 18'
1019 'action 19'
1020 'action 20'

 

[Roger Govier]

Hi

You could only use 20 levels of nested If in XL2007, and even then it would
be very unwieldy and difficult to maintain.

Far better to use a lookup table.
Create a list of your numbers in A1:A20 on sheet2, and your actions in
B1:B20 of the same sheet.
On sheet 1, in cell B1 enter
=VLOOKUP(A1,Sheet2!$A$1:$B$20,2)

 

[David Biddulph]

Instead of a nested IF, it sounds like you need a VLOOKUP. An alternative
might be to use CHOOSE. The functions are shown, with exampls, in Excel's
help.

 

[Guest]

Thanks for responding to my post quickly, Roger. I tried the method you
suggested, and it worked for me, but there's a slight problem. When I enter
an action code that is out of the the range of Action Codes (e.g. 10020), it
gives me the same result as I entered the action code 1020. Can I do
something to make it more accurate? when I enter anything other than the 20
'action codes' (1001-1020) in sheet 2, I just want the cell in 'column B' to
be to be left blank.

 

[Roger Govier]

Hi

Including the optional 4th parameter in Vlookup of False or ), would stop it
from giving the last result.
=VLOOKUP(A1,Sheet2!$A$1:$B$20,2,0)

I had omitted the optional parameter as your list is sorted, and the Vlookup
runs faster in this format (though not noticeable on a short list like
this), so I should perhaps have included it.

However, it would give a #N/A error if the value did not exits in the list.
One way around the problem would be
=IF(AND(A1>1000,A1<1020),=VLOOKUP(A1,Sheet2!$A$1:$B$20,2,0),"")

 

[Guest]

Many Thanks, Roger. I tried what you suggested, and it works. Thank you very
much for your help.