Can someone please tell me why I keep getting VLOOKUP error using IF, VLOOKUP, FIND, and ISERROR?

Question

I am programming XLS to lookup another worksheet to see whether a cell contains one of seven country codes. I found the right commands to use thanks to Yahoo! Answers user qwertykph and was able to add additional country codes by adding additional nested statements.

That was until I added my sixth country code when I kept getting a VLOOKUP error. Please insert the following code onto a cell and you will see the error.

= IF(ISERROR(FIND("IT", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))),IF(ISERROR(FIND("DE", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))), F(ISERROR(FIND("CA", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))), IF(ISERROR(FIND("AU", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))), IF(ISERROR(FIND("ES", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))), IF(ISERROR(FIND("FR", VLOOKUP(A3,'Account Information'!$A$5:$D$1764,4,FALSE))), "", "FR"), "ES"), "AU"), "CA"), "DE"), "IT")

Thanks much!

Answer 1

Nesting limit:

Creating a Name that refers to a formula "VLOOKUP(A3, $A$5:$D$10, 4 , FALSE)" would work.

1. Insert | Name> | Define...
2. Type the name, VLOOK is what I used, in the "Names in workbook" field
3. Type the formula in the "Refers to" field
4. Click OK

Now modify your formula, replacing VLOOKUP(A3, $A$5:$D$10, 4 , FALSE) with VLOOK

=IF(ISERROR(FIND("IT", VLOOK)), IF(ISERROR(FIND("DE", VLOOK)), IF(ISERROR(FIND("CA", VLOOK)), IF(ISERROR(FIND("AU", VLOOK)), IF(ISERROR(FIND("ES", VLOOK)), IF(ISERROR(FIND("FR", VLOOK)), "FR"), "ES"), "AU"), "CA"), "DE"), "IT")

It also makes your formula much shorter.

Answer 2

I am not a computer person I hope these links will help. Or call the toll free number that came with you pc or laptop.

http://www.ozgrid.com/forum/showthread.php?t=32207

http://www.ozgrid.com/forum/showthread.php?t=32504

http://www.geocities.com/marcoschmidt.geo/java-libraries-excel.html

http://filext.com/detaillist.php?extdetail=xls