VLOOKUP Function

Tassa Tenhouse

Add my vote to the pile for a VLOOKUP/HLOOKUP/MATCH option, since lookups are critcial for my particular work. Without lookups, the platform is fun but not particularly useful.

 

(Also fingers crossed for a lookup that doesn't require your key to be in the first column in a sheet -- would be nice if it accepted value, source column to look at, target column to pull from, match type).

Travis

Added, Leo and Tassa!

Mario K M

Has SmartSheet sent out a roadmap on when they will consider VLOOKUP/MATCH/HLOOKUP?

 

It's been over 6 months. 

Mario K M

So question for everyone else... it's been 6 months and it maybe another 6 months before someone talks to someone who knows someone that can get us a timeline. 

 

How is everyone trying to make it work?

 

Unless someone has a better ideas, I'm stuck with manually linking cells between SmartSheets.  Since SS doesn't have ODBC import features, I will have to periodically upload a new sheet with data I want updated and then link.  I'm hoping there is an easier way?

Travis

Mario, I do not have a timeline I can share but I can say we are currently working on a formula revamp and hope to include VLOOKUP in the upgrade.  We will post in this thread when we have more information! 

Christian A Jensen

It's vloopup that is holding my accounting department from adoption. So add my vote too.

Kristina Dojillo

Hi Travis!

 

Please add my vote for VLOOKUP, MATCH, and INDEX.

 

Thanks!

Kristina

Brad Jones

+1 vote for vlookup.  It will save me ALOT of time by not having to do manual synchronizing between sheets.

PLEASE DO THIS SOON.

Brad Jones

Travis,

 

A concern comes to mind.  You say that your devs are working on a revamp of your formula pack, but this is not mentioned in your product development roadmap (as far as I could divine).  Is it in there but called something else?

 

J. Craig Williams

 

VLOOKUP is not the same as syncing between sheets.

The basic functionality of VLOOKUP does not even need the concept of an external source.

Be careful what you wish for when the requirements are not clear.

You might be upset about what you get. 

 

Craig

Brad Jones

It is very possible here that we are operating off of definitions of 'sync' that have different constraints.  When I say sync,  I mean I want data from one sheet to update data in another sheet automatically.  This may not be an industry standard usage of the word, but it seems right to me for the action in question.

 

To be more clear:

I have data on Sheet1, the process ID is the 'search criteria' and the remainder of the supporting data is in the the columns to the right.  All of this is on one row.
I have data on Sheet2 that is not always in the same order every day, but contains the process ID and the related supporting data all in the same row in the columns to the right.  (This sheet's data is copied from an external source once per week, and will not have the data on the exact same rows each time)
I want the supporting data in Sheet1 to be automatically updated by the supporting data in Sheet2, regardless of how Sheet2 is sorted.  I can accomplish this in spreadsheet programs by setting the cells  for the supporting data in Sheet1 = VLOOKUP(data in Sheet2, based on processID).  In this way, I am making the supporting data in Sheet1 equal to the supporting data in Sheet2.  (what I meant when I said syncing)

VLOOKUP does not need an external source per se. The data I have is in two different places.  In this case I have two sheets with very different layouts because they have different customers, so my lookup would be dependent on another sheet.

 

Currently, I can link a cell in Sheet1 to a cell in Sheet2, but when the new data for Sheet2 is copied in many of my existing links will become incorrect.

If there is a way to accomplish this in SS without VLOOKUP then I am totally game for implementing it.  I know VLOOKUP works elsewhere, so...

J. Craig Williams

Brad,

 

I understand your problem.

Having done a lot of simulation in my past, when I use VLOOKUP in Excel, I was often constrained by the sorted order (looking for closest match, not exact match). Having done some programming in my past, I typically work with sorted lists (they are faster to process). 

 

What it sounds like you (and others) want is a way to reference a table structure where the column types and order are fixed but the rows are not in a fixed order - that is, something in the first (or chosen?) column is the "key" and it is NOT the row order or row ID.

This table is located somewhere else, not in the current sheet that needs to return the reference.

 

Currently, if all of this were in some part of the current sheet (not a recommended solution for this case, but for example only), when the new data is brought in, the references would be moved around. Both absolute and relative.

 

Does EVERYTHING in sheet 2 have to be lost when it is refreshed? In other words, can the importing mechanism account for the first few rows as set aside and untouched. I know there has been requests for a header section and that may be what I am talking about.

 

I'm going to stop now. I have some ideas.

 

Craig

 

 

 

 

 

 

Brad Jones

That is an interesting idea Craig.

I am not completely certain at this moment, but it 'might' be possible for me to keep some of the rows there at the top and just add on more at the end.  The process IDs are autonumbered based upon date of creation. Time to investigate.

Brett Evans

The best VLookup work around I have found is Microsoft Power BI.  I get to see the relationships between my sheets very easily.  I have not worked on pushing any info/data back to Smartsheet, but the Power BI csv exports and "synced" graphs have been more than sufficient.

Travis

Hi Everyone - I have recorded all your votes and feedback!

 

Brad, you are correct in that the formula revamp is not listed on our roadmap. Not everything we are developing will appear on the roadmap for various reasons but it's still a good source to learn about major development projects.