Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating a nested "IF" type result that requires more than 64 levels- is vlookup still the best option?

Posted on 2016-10-26
8
Medium Priority
?
65 Views
Last Modified: 2016-11-22
I'm creating a sheet that displays an organization's overall score (T1-T4) based off of up to 6 inputs (those inputs are coded as T,T-,P,P- and U). I was using nested IFs and had success until the number combinations required exceeded the levels (64). Here's a sample of the nesting lines at the 5 input level:
IF(AND(D5="T",D6="T",D7="T",D8="T",D9="T"),"T1",
IF(AND(D5="T",D6="T",D7="T",D8="T",D9="T-"),"T1",
IF(AND(D5="T",D6="T",D7="T",D8="T-",D9="T-"),"T1",
IF(AND(D5="T",D6="T",D7="T-",D8="T-",D9="T-"),"T1",
IF(AND(D5="T",D6="T-",D7="T-",D8="T-",D9="T-"),"T1",
IF(AND(D5="T-",D6="T-",D7="T-",D8="T-",D9="T-"),"T1",
IF(AND(D5="T",D6="T",D7="T",D8="T",D9="P"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="T",D9="P-"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="T-",D9="P"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="T-",D9="P-"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="P",D9="P"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="P",D9="P-"),"T2",
IF(AND(D5="T",D6="T",D7="T",D8="P-",D9="P-"),"T2",
IF(AND(D5="T",D6="T",D7="T-",D8="T-",D9="P"),"T2",
IF(AND(D5="T",D6="T",D7="T-",D8="T-",D9="P-"),"T2",
Any help or suggestions would be great!
0
Comment
Question by:John Pyne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 41860782
Can you explain, in words, the logic behind what you want to do and/or upload a sample workbook?
1
 

Author Comment

by:John Pyne
ID: 41860790
I have a table of proficiency outcomes based off of specific tasks. For example: Company A has tasks 1,2,3, and 4. The can score a rating of T,T-,P,P- and U. A combination of those scores creates an overall proficiency level (T1,T2,T3,T4). See attached table for partial combinations.

I'd like to create a spreadsheet that would allow me to input the task scores and have a proficiency level generated automatically. Nesting IF worked great...but I quickly exceeded the 64 levels and it was a bit tedious.
Capture.JPG
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points (awarded by participants)
ID: 41860794
Make a list of the possible combinations (column A), and the result for each (column B).
TTTTT   T1
TTTTT-   T1
TTTT-T-   T1
..
TTTPP   T2
etc.
Then do a lookup for D5&D6&D7&D8&D9 on that.
3
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 46

Expert Comment

by:aikimark
ID: 41862211
@John Pyne

Do you also need to score the other combinations (sets of 2, 3, 5, 6)?  Or is this limited to just sets of four?
0
 

Author Comment

by:John Pyne
ID: 41862235
@aikimark,
Combinations sets 3-6 need to be scored.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41862591
In that case, Ejgil Hedegaard probably posted the best solution.   Use a different worksheet to past the strings and score value table(s).

Alternatively, you could populate a dictionary object in a function and invoke that function in a formula.  The formula would be very simple (no If statements) and the VBA function would be relatively simple, too.  If you have access to a digital version of the table data, it will save you a lot of typing.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41872599
Another option would be to close out each IF statement rather than nesting them and concatenate the results; if only result is Ture you wll get the result from that secton.

IF(AND(D5="T",D6="T",D7="T",D8="T",D9="T"),"T1","")&
IF(AND(D5="T",D6="T",D7="T",D8="T",D9="T-"),"T1","")&....

Alternatively, rather than using AND, concatenate the values and compare to one string:

IF(D5&D6&D7&D8&D9="TTTTT","T1","")&
IF(D5&D6&D7&D8&D9="TTTTT-","T1","")&....

Another option:

="T"&COUNTIF(D5:D9,"P*")+1

Might need some expanding for the U variants.....
1

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question