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.

 

[Jonmo1]

You'll need 1 entry in your table for every code.

 

[Jonmo1]

Of if you can drop the - from the end of the codes, and sort them in ascending order then it would work.
Example to follow.

 

[Jonmo1]

Something like this

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

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,E1:F10)

 

[Badge83]

Im close but still not quite there. This is what I have so far.

=VLOOKUP(A2003&"*",Lists!B4:J68,1,FALSE)

I have sheet 1 that I enter a code and sheet 2 (Lists) that has all the corresponding codes to customers. The problem is that I want to look through several column's in sheet 2 with codes in say column B, C, D, etc, with column A with customer data. So that when I enter a code in sheet 1, data is automatically filled in due to the code in sheet 1 matching to the code in sheet 2 and the customer data in column A.

For example, if I enter 1458-0034 in sheet 1, sheet 2 can pick up on the first part of the code (145 and place the customer data in a chosen cell in sheet 1. But the formula i have entered places the first customer name and does not recognised and codes I enter into sheet 1. What have I done wrong?