VLOOKUP Questions

I've got a basic handle on VLOOKUP but what I am trying to do is a little unconventional due to sheet row limitations within smartsheet.

We have a sheet in which people will fill in a part number, each part number has a part description. What I want to do is automate the part description field. So I want to use a VLOOKUP to reference other sheet to match the part number of the row and fill in the corresponding part description.

My issue starts with the fact that we have other 15,000 parts and part descriptions. So when I imported the data from excel into smartsheet I had to do so in 4 separate sheets due to the 5000 row limit Smartsheet has.

I cant seems to write a functioning VLOOKUP to search the part number field of all 4 sheets no matter how I configure the syntax of the VLOOKUP.

I've read on some documentation https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets?_ga=2.267643466.530744127.1534772995-1133749180.1518182414

This makes it seem possible, was hoping for some help.

Find more posts tagged with

Formulas

Comments

Shawn R

As always I seem to find a solution shortly after posting the question. I was able to get my VLOOKUP to work when referencing multiple sheets using the =IF(ISERROR, followed by the 4 separate VLOOKUP formulas.

However having to reference two columns in 4 sheets that total over 15,000 rows means I exceeded the limit of referencing 25,000 cells by the time I reference the third sheet.

So it looks like the limitations of smartsheet are not going to allow me to automate this process.

J. Craig Williams

Shawn,

My post here does not imply I have solution, but ...

1. Are your part numbers ordered?

2. Are new ones being added? (How often?)

3. Is this only two columns of concern - [Part Number] and [Description]?

Craig

Shawn R

Craig,

1. Yes our part numbers are ordered. However, they start with an abbreviation to the customer name. Examples would be: ABC00001, ABC00002, XYZ00003, etc.

2. New ones can be added frequently or infrequently. It really all depends, if I had to give a rough average I would say maybe 1-2 a month.

3. Yes this is correct, there are only the [Part Number] and [Description] that is of concern.

J. Craig Williams

Thanks Shawn.

AppSheet released a feature in the past year that allows a large table to be broken up into smaller chunks and then references in their tool, which integrates well with Smartsheet.

I have not used it, as it did not meet my requirements, but it might meet yours.

I have a Smartsheet only solution percolating in the back of my head (I can feel the hairs standing up and my ears itch), but it is not yet fully formed. When/if it does, I'll post here.

Craig

Shawn R

Craig,

I will check out AppSheet here today at some point. I look forward to your potential Smartsheet solution if it does form. Thanks!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…