Populate Cell Value based on Defined List

StuartTAG

Hi,

Im trying to figure out the below scenario.

SHEET 1 - ROW 1 - CELL 1

Drop Down list of Fruit

SHEET 1 - ROW 1 - CELL 2 (DESCRIPTION)

EMPTY

SHEET 1 - ROW 1 - CELL 3 (PRICE)

EMPTY

SHEET 2

SOURCE SHEET

Scenario

Open SHEET 1, Click CELL 1, choose APPLES. CELL 2 populates with "GREEN APPLES" mapped from SHEET 2, CELL 3 populates with "$3.00", mapped from SHEET 2.

Im sure this is very easy, but I cannot figure it out.

Thanks

Stuart

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You are going to want to use an INDEX/MATCH.

=INDEX({Source Sheet Column To Pull}, MATCH([Column to Match]@row, {Source SHeet Match Column}, 0))

Mark.poole

@StuartTAG

You can achieve this with index match. in each row you want to automatically populate based on the dropdown.

Create references to sheet 2 for the columns your matching too. for the sake of this post I will call these references

Description and Price as well as a key that matches each item to its description and price in the second sheet. I will call this one key.

In column 2 Use something similar to this formula.

=INDEX({Type},Match([CELL 1]@row, {Key},0))

Use this in column 3

=INDEX({Price},Match([CELL 1]@row, {Key},0))

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.