• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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

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
John Pyne
Asked:
John Pyne
1 Solution
 
NorieData ProcessorCommented:
Can you explain, in words, the logic behind what you want to do and/or upload a sample workbook?
1
 
John PyneAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
Industry Leaders: 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!

 
aikimarkCommented:
@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
 
John PyneAuthor Commented:
@aikimark,
Combinations sets 3-6 need to be scored.
0
 
aikimarkCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now