Is there a column or range limit to Vlookups?

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.

Find more posts tagged with

Project Management

Comments

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|

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]

Sign in to join the conversation!

Quick Links

Trending discussions

How to prevent or recover deletion of rows
Is it possible to prevent the deletion of rows in a sheet? Specifically, in cases where a user legitimately requires editor permission to the underlying sheet and where the row cannot be locked (as continued editting of the row is required.) If not, what are some strategies for recovering a row that has been inadvertently…
Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…