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

Tim Wing

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.

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

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

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. 👍️