Go Premium for a chance to win a PS4. Enter to Win

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
?
73 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
8 Comments
 
LVL 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

971 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