Solved

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

Posted on 2016-10-26

1 solution

58 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!

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

Join The Community

8 Comments

LVL 34

Expert Comment

by:Norie

ID: 418607822016-10-26

Can you explain, in words, the logic behind what you want to do and/or upload a sample workbook?

Author Comment

by:John Pyne

ID: 418607902016-10-26

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

LVL 22

Accepted Solution

by:Ejgil Hedegaard

Ejgil Hedegaard earned 500 total points (awarded by participants)

ID: 418607942016-10-26

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.

LVL 46

Expert Comment

by:aikimark

ID: 418622112016-10-27

@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?

Author Comment

by:John Pyne

ID: 418622352016-10-27

@aikimark,
Combinations sets 3-6 need to be scored.

LVL 46

Expert Comment

by:aikimark

ID: 418625912016-10-27

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.

LVL 33

Expert Comment

by:Rob Henson

ID: 418725992016-11-03

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.....

Featured Post

Question has a verified solution.

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

Microsoft Excel - Creating Drop-Down lists that depend on the value of another list(s)

Article by: redrumkev

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…

Microsoft Excel

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Creating a Cool Heat Map in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Microsoft Excel

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…