# Do Vlookups in code

Closed
Corina - Jun 9, 2010 at 06:18 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 10, 2010 at 01:27 AM
Hi

I do not know if it is possible to do this, actually I am trying to bring different values from different tables with the vlookup function but excel doesn't let me use more than 7 vlookups, do you think it could be possible to do this by code.

My formula is this:

=IF(J3=1,VLOOKUP(WorkSheet!A5,WorkSheet!A5:F17,2,FALSE),IF(J3=2,VLOOKUP(WorkSheet!H5,WorkSheet!H5:M17,2,FALSE),IF(J3=3,VLOOKUP(WorkSheet!A23,WorkSheet!A23:F35,2,FALSE),IF(J3=4,VLOOKUP(WorkSheet!H23,WorkSheet!H23:M35,2,FALSE),IF(J3=5,VLOOKUP(WorkSheet!A41,WorkSheet!A41:F53,2,FALSE),IF(J3=6,VLOOKUP(WorkSheet!H41,WorkSheet!H41:M53,2,FALSE),IF(J3=7,VLOOKUP(WorkSheet!A59,WorkSheet!A59:F71,2,FALSE),"Empty")))))))
And so on until the number 14

The name of my sheets are this: "WorkSheet" (where are all the tables) and "Sku"

If anyone know how to do this please explain me how, please..!!!

Corina :)

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 06:40 PM
It is not VLOOKUP that prevents. it is IF statements. You cannot have more than 7 nested ifs. So either break up the the if block to no more than 7 by using more than 1 columns.

Other option would be to use a user defined function
Hi ,, Thanks but what is the user defined funcition
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 10, 2010 at 01:27 AM
user defined function is what is says a user defined function. Some functions are inbuilt in excel. and an excel user can create his or her own function too. It is like sub, other than it returns value

Function myFunction(......) as String/variant or ...

ENd Function