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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!