20 level nested If

H

Hi_no_Tori

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'
 
R

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

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.
 
H

Hi_no_Tori

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.
 
R

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),"")
 
H

Hi_no_Tori

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top