Is there a column or range limit to Vlookups?

JHann

Is there a column or range limit for Vlookups?    My Weeknumber I'm referencing is in column 1 and the number I need is in column 33.     The error its giving me is #Invalid operation.     It seems that I can handle 31 columns but anything after that, it gives that message.

L_123

There is not a column limit for vlookup. That said you are putting a lot of stress on the program and it might simply be functioning very slowly. Also if there is an error in the range it could cause an issue.

 

I recommend looking into index(match()). It is much less resource intense, especially with the larger ranges, and it is less likely to fail due to issues inside the range as it is much more direct.

JHann

Thank you for the help.    I'll look into that. 

L_123

it functions the same way as excel. If you have any difficulties finding resources for smartsheet, then look up how it works in excel, there are hundreds of really good resources that can show you how this works.

 

I've written a quick summary below of how index(match() functions

Match("Text to search for",[Range to search]:[Range to search],0)

Will search the column [Range to search] for "Text to search for". Once it finds this text, it will report the number of cells it had to search in order to find it.

Dataset:

---------------------------------------------

|Range to search|

1

2

Text to search for

 

------------------------------------------

The match formula would return the number 3

index([Return Range]:[Return Range],X)

returns X value from Return Range. If X= 3, then it will return the third value. You put these together

index([Return Range]:[Return Range],Match("Text to search for",[Range to search]:[Range to search],0))

It will find which value in [Range to search] is "Text to search for" and return it as 3 for the above dataset. Index will return the 3rd value in [Return Range]