nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Welcome!

VLOOKUP Confusion

jiwhite

I'm very confused about how VLOOKUP has been implemented.

Can the search value reference a value in another column in the current row, or can it only be a string enclosed in quotes? If it can only be a string, it makes the function pretty useless because I'd like to be able to lookup information based on data entered in a Form.
Why does the column searched in the lookup table need to be the leftmost column? Why isn't this a parameter to allow searching existing sheets that were created before the function was implemented or that may need to be searched in multiple ways? Can the lookup table reference a report, which could alter the order of columns?

Find more posts tagged with

Formulas

Comments

Daniel Stein

Thanks for the questions.

Yes, the search value can be a reference.
While VLOOKUP requires the search term to be in the leftmost column, as an alternative you can use INDEX and MATCH to use any columns for your search and return values. I included information below about how to use them to perform lookups.
You can use these functions to perform lookups on any sheets, including your existing sheets.
Formulas can't reference data in reports at present.

Typical usage of INDEX and MATCH to perform lookups:

=INDEX(return_column, MATCH(search_value, lookup_column, 0))

Summary: Looks up a value and returns a corresponding value from the same row but a different column (same as VLOOKUP)
Arguments
- return_column: The column of the value to return
- search_value: The value to search for
- lookup_column: The column in which to search

jiwhite

Index and Match worked for what I need. Thanks!

Victor Sauceda

Whats the different between the return column and the lookup column??

Sign in to join the conversation!

Quick Links

Trending discussions

Identifying which calendar quarters a project is active (last quarter, this quarter, next quarter)
I'm building executive reports to show which projects are active by quarter. I have start and end dates for each project to show the active date range. The output I'm looking for is a report showing what was active last quarter. Another report showing what is active this quarter. And what will be active next quarter.…
Smartsheet Automation
Hello, I have automations enabled in several spreadsheets where, whenever any value is entered in a specific column, the entire row should be copied to another spreadsheet. The automation is configured so that the row is copied whenever the automation is triggered. However, I identified several cases that were properly…
Pulling multiple rows from one sheet to another based on value in a column.
Hello! I'm sure this is a basic question that I'm just missing the how-to on. Our orginization has a master schedule for the year. I need to take from that master schedule and populate a different sheet based on a date. For example, master schedule has a column of dates. Multiple rows will contain that date. I need to pull…