Nesting limit reached. How to exceed past 64 nesting IF formula's.

Badge83

New Member
Joined
Jun 23, 2015
Messages
6
Hi, I am creating a database for my office that auto fills most of the information when entering a particular code that corresponds to a customer. IE the first 4 digits in a cell entry would be the customer code and the other 4 would be the job reference (1465-0045). A separate cell with multi IF formulas will correspond to another sheet with a list of customers and other information. The problem is that there is more then 64 customers, allowing only 64 IF nesting formulas as show below.

=IF(COUNT(SEARCH({"1484-"},A235)),Lists!B$4,IF(COUNT(SEARCH({"1542-","1734-"},A235)),Lists!B$5,IF(COUNT(SEARCH({"0352-"},A235)),Lists!B$6,IF(COUNT(SEARCH({"1191-","1709-"},A235)),Lists!B$7,IF(COUNT(SEARCH({"1732-"},A235)),Lists!B$8,IF(COUNT(SEARCH({"1234-"},A235)),Lists!B$9,IF(COUNT(SEARCH({"1736-"},A235)),Lists!B$10,IF(COUNT(SEARCH({"1392-"},A235)),Lists!B$11,IF(COUNT(SEARCH({"1554-"},A235)),Lists!B$12,IF(COUNT(SEARCH({"1635-"},A235)),Lists!B$13,IF(COUNT(SEARCH({"1322-"},A235)),Lists!B$14,IF(COUNT(SEARCH({"1555-","1654-"},A235)),Lists!B$15,IF(COUNT(SEARCH({"1743-"},A235)),Lists!B$16,IF(COUNT(SEARCH({"1660-"},A235)),Lists!B$17,IF(COUNT(SEARCH({"1353-"},A235)),Lists!B$18,IF(COUNT(SEARCH({"1375-","1214-"},A235)),Lists!B$19,IF(COUNT(SEARCH({"1245-"},A235)),Lists!B$20,IF(COUNT(SEARCH({"1702-"},A235)),Lists!B$21,IF(COUNT(SEARCH({"1686-","1466-"},A235)),Lists!B$22,IF(COUNT(SEARCH({"1543-","1675-"},A235)),Lists!B$23,IF(COUNT(SEARCH({"1490-"},A235)),Lists!B$24,IF(COUNT(SEARCH({"1113-"},A235)),Lists!B$25,IF(COUNT(SEARCH({"1708-"},A235)),Lists!B$26,IF(COUNT(SEARCH({"1679-"},A235)),Lists!B$27,IF(COUNT(SEARCH({"1685-","0551-","1518-","1396-","1724-"},A235)),Lists!B$28,IF(COUNT(SEARCH({"1745-"},A235)),Lists!B$29,IF(COUNT(SEARCH({"1550-"},A235)),Lists!B$30,IF(COUNT(SEARCH({"1742-"},A235)),Lists!B$31,IF(COUNT(SEARCH({"1703-"},A235)),Lists!B$32,IF(COUNT(SEARCH({"1470-","1754-"},A235)),Lists!B$33,IF(COUNT(SEARCH({"1246-"},A235)),Lists!B$34,IF(COUNT(SEARCH({"1422-"},A235)),Lists!B$35,IF(COUNT(SEARCH({"0166-"},A235)),Lists!B$36,IF(COUNT(SEARCH({"1523-"},A235)),Lists!B$37,IF(COUNT(SEARCH({"0723-","1423-"},A235)),Lists!B$38,IF(COUNT(SEARCH({"1704-"},A235)),Lists!B$39,IF(COUNT(SEARCH({"1193-"},A235)),Lists!B$40,IF(COUNT(SEARCH({"1730-","0543-"},A235)),Lists!B$41,IF(COUNT(SEARCH({"1186-"},A235)),Lists!B$42,IF(COUNT(SEARCH({"1305-"},A235)),Lists!B$43,IF(COUNT(SEARCH({"0542-"},A235)),Lists!B$44,IF(COUNT(SEARCH({"1632-"},A235)),Lists!B$45,IF(COUNT(SEARCH({"1653-"},A235)),Lists!B$46,IF(COUNT(SEARCH({"1746-"},A235)),Lists!B$47,IF(COUNT(SEARCH({"1435-"},A235)),Lists!B$48,IF(COUNT(SEARCH({"1467-","1474-","1528-","1752-"},A235)),Lists!B$49,IF(COUNT(SEARCH({"1159-"},A235)),Lists!B$50,IF(COUNT(SEARCH({"1481-"},A235)),Lists!B$51,IF(COUNT(SEARCH({"1185-"},A235)),Lists!B$52,IF(COUNT(SEARCH({"1513-"},A235)),Lists!B$53,IF(COUNT(SEARCH({"1224-"},A235)),Lists!B$54,IF(COUNT(SEARCH({"1718-"},A235)),Lists!B$55,IF(COUNT(SEARCH({"1623-"},A235)),Lists!B$56,IF(COUNT(SEARCH({"1651-"},A235)),Lists!B$57,IF(COUNT(SEARCH({"1143-"},A235)),Lists!B$58,IF(COUNT(SEARCH({"1621-"},A235)),Lists!B$59,IF(COUNT(SEARCH({"1357-"},A235)),Lists!B$60,IF(COUNT(SEARCH({"1155-"},A235)),Lists!B$61,IF(COUNT(SEARCH({"1321-"},A235)),Lists!B$62,IF(COUNT(SEARCH({"0523-"},A235)),Lists!B$63,IF(COUNT(SEARCH({"1449-"},A235)),Lists!B$64,IF(COUNT(SEARCH({"1112-","1269-","1395-","1108-","1758-","1755-","1757-","1759-"},A235)),Lists!B$65,IF(COUNT(SEARCH({"1496-","1738-","1756-","1760-"},A235)),Lists!B$66," ")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

I've read up that this can be solved using VLOOKUP, but do not know where to start with this. Or if there is another way please could you let me know. Thank you.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
just had a quick play with a VLOOKUP and I'm not sure its as easy as that
IF(COUNT(SEARCH({"1542-","1734-"},A235)),Lists!B$5
IF(COUNT(SEARCH({"1112-","1269-","1395-","1108-","1758-","1755-","1757-","1759-"},A235)),Lists!B$65
refer to the same line @ B5

unless someone has a smart answer with indirect/offset to allow that to be hard coded
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
just had a quick play with a VLOOKUP and I'm not sure its as easy as that
IF(COUNT(SEARCH({"1542-","1734-"},A235)),Lists!B$5
IF(COUNT(SEARCH({"1112-","1269-","1395-","1108-","1758-","1755-","1757-","1759-"},A235)),Lists!B$65
refer to the same line @ B5

unless someone has a smart answer with indirect/offset to allow that to be hard coded
Something like this
<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1395-</td><td style=";">value of b65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1542-</td><td style=";">value of B5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1734-</td><td style=";">value of B5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1112-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1269-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1395-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1108-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1758-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1755-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1757-</td><td style=";">value of b65</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1759-</td><td style=";">value of b65</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=VLOOKUP(<font color="#0000FF">A1,F1:G10,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Badge83

New Member
Joined
Jun 23, 2015
Messages
6
Just so you are aware, I am using Excel 2010. I have recently read that Excel 2016 has a new formula function called IFS. Which is meant to get around the nesting problem. Has anyone used this and does it work?
 

Badge83

New Member
Joined
Jun 23, 2015
Messages
6
Thank you Joe4, I will look this up and see if VLOOKUP can solve my nesting issue.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Yes, the IFS function can test up to 127 conditions.
However, it is generally agreed that using some sort of lookup table is far more efficient, and easier to manage than so many if's in one formula.

Also, it requires an Office 365 subscription, not just Office 2016.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
Also, it requires an Office 365 subscription, not just Office 2016.
and the subscription method is the best way to go IMHO. You get the ability to install the desktop version and do not have to upgrade as soon as the latest version is released.
There is one difference for any user as it relates to PowerPivot, which you can upgrade a subscription later, but to upgrade a boxed-install you would buy a whole new license.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
I think a PivotTable as an intermediary source could be helpful. Then use a GETPIVOTDATA formula.
The sheet holding the PivotTable could be hidden. I would use an Event to always update the Pivot Table when the Inputs for the formula are changed to ensure the GETPIVOTDATA is returning the up-to-date value.
 

Badge83

New Member
Joined
Jun 23, 2015
Messages
6
So far by the links below from Joe4, I have come up with this VLOOKUP.

=VLOOKUP(A2002,Sheet1!A1:E3,4,TRUE)

But as you can see i need to look up on a range of columns that have several codes (EG 0166-) for each customer. Unfortunately the above VLOOKUP will not work if I put in for example 0166-0034. I thought TRUE would pick up on the first part of the code and correspond to the customer name. TRUE = approximate match. Like in my IF formula.

Example below.

Row A Row B Row C Row D

Code 1 Code 2 Code 3 Customer 1
Code 1 Code 2 Code 3 Customer 2
Code 1 Code 2 Code 3 Customer 3


Welcome to the Board!

I would recommend creating a lookup table and using VLOOKUP.
See: https://www.techonthenet.com/excel/formulas/vlookup.php
and https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
for details and examples.

Or you could create a User Defined Function in VBA.
 

Forum statistics

Threads
1,081,986
Messages
5,362,560
Members
400,681
Latest member
mariscann

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top