Can I pull a specific value into a cell, if the value in the cell next to it is found in a list?

I am hoping to pull in a specific value from a list that corresponds to the value entered in the cell next to it. For the below example, if the "store" = 0323, then pull in the "group" # from the "group data" # that is next to that "store data" # in a list (i.e. 290).

Using the formula =IF(Store@row = [Store Data]@row, [Group Data]@row, " "), will pull in the "group data" # into the "group" cell if it's on the same line, but it's not referencing a list so it wont grab the correct "group" # if its on a different line.

I was able to find a formula for excel (shown below- user enters "store #" and sheet populates the correct "Group") but just haven't been able to find the right one for Smartsheet yet.

I am also in favor of using a reference sheet if that would simplify setting this up.

Any help would be much appreciated!!

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @Ace012

Yes, anytime you have a static list then a look-up reference sheet will help you.

You will use an INDEX/MATCH (vs an Index/Collect) because you only have one criteria (the store data) to match to your group data.

Because the reference sheet is a different sheet that your active data sheet, you'll have to create the 'cross sheet' references using the link 'Reference another sheet' in your formula window. Remember you can rename these references before inserting them- ie, you don't have to use the generic sheet name 'Range 1' name that will populate.

Your formula on your active sheet will look something like this. Your cross sheet reference names will be different

=INDEX({reference sheet Group Data column}, MATCH(Store@row, {reference sheet Store Data column},0))

If you are unfamiliar with cross sheet references, you can find more info here.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Kelly

All comments

Kelly Moore

Hey @Ace012

Yes, anytime you have a static list then a look-up reference sheet will help you.

You will use an INDEX/MATCH (vs an Index/Collect) because you only have one criteria (the store data) to match to your group data.

Your formula on your active sheet will look something like this. Your cross sheet reference names will be different

=INDEX({reference sheet Group Data column}, MATCH(Store@row, {reference sheet Store Data column},0))

If you are unfamiliar with cross sheet references, you can find more info here.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Kelly

Ace012

Thank you so much Kelly, that worked perfectly!!😎

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.