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

[Badge83]

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.

 

[Joe4]

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.

 

[mole999]

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]

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

Unknown
	A	B	C	D	E	F	G
1	1395-	value of b65				1542-	value of B5
2						1734-	value of B5
3						1112-	value of b65
4						1269-	value of b65
5						1395-	value of b65
6						1108-	value of b65
7						1758-	value of b65
8						1755-	value of b65
9						1757-	value of b65
10						1759-	value of b65
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,F1:G10,2)

 

[Badge83]

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]

Thank you Joe4, I will look this up and see if VLOOKUP can solve my nesting issue.

 

[Jonmo1]

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]

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]

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]

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.