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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/09/22 Answer ✓

    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))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!