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
55 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 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 45

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 45

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

729 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