Vlookup

SIRROSS101

what is the best way to look up between smartsheets and keep the data fresh

=VLOOKUP([Equipment ID]@row, {Equipment Tracker H5 RM 8.8.24}, 1, false)

Equipment ID]@row = equipment id this sheet

{Equipment Tracker H5 RM 8.8.24} = large data block in another sheet

this works but will not refresh if data changes

help

Find more posts tagged with

Sheets

Automations

Project Management

Cell linking

Account and User Management

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @SIRROSS101

You are using a cross-sheet cell reference.

By selecting whole columns, you can cope with any added rows that did not exist when the range was created.

Column A gets a "#NO MATCH" error when the formula references the row outside the cell range reference.

Adrian Mandile CHESS

Hi @SIRROSS101, @jmyzk_cloudsmart_jp,

I agree with the suggestion for whole column external references instead of a range of cell references, and I would go one step further, by suggesting that you use an INDEX(MATCH()) combination formula instead of VLOOKUP().

The benefit is in that because VLOOPUP allows for a multi-column range and indicates which column to use in order from left to right, it is vulnerable to changes in the structure/position of columns in the source lookup sheet.

Using a combination of INDEX & MATCH with single-column range references, could make your formula more robust – more like a two-dimensional lookup (similar in concept to the relatively new XLOOKUP function in Excel). But, given that Smartsheet is a database and therefore 'one'-dimensional, it is best to use single-column ranges (without needing to calculate/determine the lookup column range via a formula parameter), This will avoid any likelihood of errors in the external lookup column, and makes the formula clearer to read with regards to the result column.

Try: =INDEX( [result column range], MATCH( [lookup value column]@row, [lookup column range], 0) )

PS: As a guide for naming many external column references, I use 'SheetName_ColumnName' (no spaces).

jmyzk_cloudsmart_jp

I agree with @Adrian Mandile CHESS on the robustness of the INDEX(MATCH() formula.

However, VLOOKUP in Smartsheet is ideal for quickly creating solutions because it allows you to reference a single, continuous range without the need to define individual ranges for lookup and return columns. This makes setup faster and more straightforward, especially when your data is structured with the lookup column on the left. It's perfect for straightforward lookups where speed is a priority.

As SIRROSS101's example, we may have to potentially set up 6 (A to F) ranges, which takes time.

I realized the benefit of answering this question: Inquiry How to vlookup

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.