Setting up VLOOKUP correctly

Hello,

I have a source sheet that has a unique ID, and I use that as a reference to generate a number of references in a second sheet for extra details. I am using VLOOKUP to bring about 10 columns from the source sheet into the second sheet. The typical formula looks like this:

=VLOOKUP([AOP ID]@row, {2023 AOP Entry Range 1}, 7, false)

where the range is in the source sheet. I get why, but every time I insert a new column into the source table, it messes up my absolute column reference (in this case above, "7") and I have to go back a fix every vlookup. So now to the question:

I know there are some ways in excel do to INDEX and MATCH so I don't break my formulas every time. Is there something similar in SmartSheet? Apologies for the amateur question. I'm still getting my SmartSheet legs.

Thanks,

Brian

Find more posts tagged with

Formulas

Accepted answers

All comments

Michael Culley

Yes INDEX MATCH exists in smartsheet and I highly recommend it for the reasons you stated.

=INDEX({data you want},MATCH(search value,{range},0))

Michael Culley

So something like: =INDEX({2023 AOP Entry Range 1},MATCH([AOP ID]@row,{AOP ID from other sheet},0))

bigbluebottle

Yeah that's it. And I actually just found the answer in the help. I pulled the trigger and asked too soon. Thanks!

https://help.smartsheet.com/function/index

bigbluebottle

And a better detailed explanation here. https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation/p1

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.