Nested If Statement with 15 values

[mbajmca]

Hi All,

I am trying to create an if statement that has 15 values. If none of those values are true, the cell would have a space (" ") in it. If one is true, it would equal a value of 1-15 and the others would be false. This is the best I could come up with and now I am asking for your help.

=IF(AR!J4 = "Add",1," ",IF(AR!J4 = "Update/Display",2, " ",IF(AR!J4 = "Add Update/Display",3, " ",IF(AR!J4 = "Update/Display All",4, " ",IF(AR!J4 = "Add Update/Display All",5, " ",IF(AR!J4 = "Update/Display Update/Display All",6, " ",IF(AR!J4 = "Add Update/Display Update/Display All",7, " ",IF(AR!J4 = "Correction",8, " ",IF(AR!J4 = "Add Correction",9, " ",IF(AR!J4 = "Update/Display Correction",10, " ",IF(AR!J4 = "Add Update/Display Correction",11, " ",IF(AR!J4 = "Update/Display All Correction",12, " ",IF(AR!J4 = "Update/Display Update/Display All Correction",14, " ",IF(AR!J4 = "Add Update/Display Update/Display All Correction",15, " "))))))))))))))

The error I get with this is "Too many Arguments with this Function"

Thanks for your help

[Richard Buttrey]

Whoa, whoa whoa there.

Whenever you find yourself passing a handful of IF statements think again. Almost always there is a better way.

Have you for instance considered a VLOOKUP, or a LOOKUP of some description or perhaps the common INDEX(MATCH()) combination?

... and welcome to the forum by the way...

[Jonmo1]

Make a list of all they key phrases (in the order of the rank 1-15)
Say in Z1:Z15
Z1 = Add
Z2 = Update/Display
Z3 = Add Update/Display
etc..

Then use
=IFERROR(MATCH(AR!J4,$Z$1:$Z$15,0),"")

[mbajmca]

Thanks for the quick reply. I have not used those functions. I haven't worked with them yet. I will take a look and see what I can come up with.

[mbajmca]

Thanks Jonmo1. When I enter in that formula, I don't get any values in the cells.

=IFERROR(MATCH(AR!J4,'Display Data'!$A$1:$A$15,0)-1,"")

[Sam Capricci]

this would be using lookup...
=LOOKUP(AR!J4,{"ADD","UPDATE/DISPLAY","ADD UPDATE/DISPLAY","UPDATE/DISPLAY ALL","ADD UPDATE/DISPLAY ALL","UPDATE/DISPLAY UPDATE/DISPLAY ALL","ADD UPDATE/DISPLAY UPDATE/DISPLAY ALL","CORRECTION","ADD CORRECTION","UPDATE/DISPLAY CORRECTION","ADD UPDATE/DISPLAY CORRECTION","UPDATE/DISPLAY ALL CORRECTION","UPDATE/DISPLAY UPDATE/DISPLAY ALL CORRECTION","ADD UPDATE/DISPLAY UPDATE/DISPLAY ALL CORRECTION"},{1,2,3,4,5,6,7,8,9,10,11,12,14,15})
I think I caught all of your conditions.
still a vlookup might be much easy.

[Jonmo1]

Which sheet contains the list you created of all the phrases?

Also, the -1 was a little mistake, it should just be
=IFERROR(MATCH(AR!J4,'Display Data'!$A$1:$A$15,0),"")

And your list of phrases must be in A1:A15 on the sheet named Display Data

[mbajmca]

Thanks Sambo Kid. The look up brings back a value of 11, but the value is incorrect. Cell AR!J4 has a value of Update/Display so it should be 2. I am trying to figure out why.

[mbajmca]

Thanks Jonmo1! That solved my issue. Part of the problem was on the Display Data sheet there were extra spaces after the criteria (I guess my source material that I copied from had spaces), once I changed the formula to removed the -1 and the extra spaces, it came back with the correct values. It really helps to keep things simple.

Thank you all so much for your help. I would have been beating my head against the wall for hours trying to figure this out.

[Jonmo1]

You're welcome.

FYI, the lookup didn't work because Lookup requires the data in the first array to be sorted in ascending order.

[Sam Capricci]

I see you marked it solved, but here is a better way than what I gave you earlier using a vlookup for what it's worth.