Matching values between column and row between sheets and returning corresponding value

First post. Struggling with this one.

On Sheet 1, I want to compare the week number from a column on sheet 1, to the week number in a row (row1) on sheet 2 and then return the value, in this case a symbol from row 4 on sheet 2 .

Sheet1 -

Sheet 2 -

Am only showing part of week number range. But in this case am I after - if week number on sheet 1 at cell row2 matches cell in week number row (row1) on sheet2, return symbol from cell at row 4 sheet 2. So return Green.

Is this possible. Tried Index/Match, but maybe could work with IF function? Any help gratefully received.

Find more posts tagged with

Accepted answers

Paul Newcome

Will it always be row 4?

If so, you are on the right track with INDEX/MATCH. Your first range would be row 4. The second portion of the INDEX function would be 1 since you are only referencing a single row. The third portion of the INDEX function that determines which column to pull from is where you would use your MATCH.

Edit to add example:

=INDEX({Row 4}, 1, MATCH(Weeknumber@row, {Row 1}, 0))

All comments

Paul Newcome

Will it always be row 4?

Edit to add example:

=INDEX({Row 4}, 1, MATCH(Weeknumber@row, {Row 1}, 0))

Tim Wing

Thank you so much Paul.

It was the 1 for the single row that I was missing. It will always be Row 4 to return the value to the cell at row 2 on sheet 1, but then it will be Row 5 for cell at row 3 on sheet 1, etc, etc. I will therefore just copy down and edit the row ref.

Thanks again, much appreciated.

Tim.

Paul Newcome

Happy to help. 👍️

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.