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.
Course Of The Month
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Creating a nested "IF" type result that requires more than 64 levels- is vlookup still the best option?
Posted on 2016-10-26
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
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T-",D7="
IF(AND(D5="T-",D6="T-",D7=
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Any help or suggestions would be great!
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T-",D7="
IF(AND(D5="T-",D6="T-",D7=
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Any help or suggestions would be great!
[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
2-
2
- +2
8 Comments
Active today
Can you explain, in words, the logic behind what you want to do and/or upload a sample workbook?
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
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
Active today
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.
TTTTT T1
TTTTT- T1
TTTT-T- T1
..
TTTPP T2
etc.
Then do a lookup for D5&D6&D7&D8&D9 on that.
Active today
@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?
Do you also need to score the other combinations (sets of 2, 3, 5, 6)? Or is this limited to just sets of four?
Active today
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.
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.
Active today
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
IF(AND(D5="T",D6="T",D7="T
Alternatively, rather than using AND, concatenate the values and compare to one string:
IF(D5&D6&D7&D8&D9="TTTTT",
IF(D5&D6&D7&D8&D9="TTTTT-"
Another option:
="T"&COUNTIF(D5:D9,"P*")+1
Might need some expanding for the U variants.....
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Alternatively, rather than using AND, concatenate the values and compare to one string:
IF(D5&D6&D7&D8&D9="TTTTT",
IF(D5&D6&D7&D8&D9="TTTTT-"
Another option:
="T"&COUNTIF(D5:D9,"P*")+1
Might need some expanding for the U variants.....
Featured Post
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.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll
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.