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

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

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!