Index Match on same sheet only matches to the first key found at top of row
The Index Match on same sheet only matches to the first key found at top of row where I am expecting it to look at all rows in the sheet. Here's the formula in Col3.
=INDEX([Capacity 1]@row, MATCH([Collapsing 1]@row, Key:Key, 0))
And the example is below:
You can see that the ABC12345 under Collapsing 1 col matches to the first row under the Key col and the number 14 correctly displays in Col 3.
However, the AERIOU567 on row 2 Collapsing 1 column does not match to the last row and column Key AERIOU567. Instead, I get an #INVALID VALUE error.
My expectations is that there should be a '2' in Col 3 instead of the error.
Does anyone have an idea on what I am doing incorrectly?
.
Best Answer
-
I believe the index match is supposed to follow this format:
=INDEX({range of cells you want to output}, MATCH([row to match]@row, {range of cells to match to row}, 0))
You wrote it with the beginning of the index as a row, so it can only pull from that row, which is why ABC alone worked. Instead, you should adjust it slightly to be a range:
=INDEX([Capacity 1]:[Capacity 1], MATCH([Collapsing 1]@row, Key:Key, 0))
I hope this helps~
Renée Roberge
Answers
-
You need to specify the entire Capacity 1 column.
=INDEX([Capacity 1]:[Capacity 1], MATCH([Collapsing 1]@row, Key:Key, 0))
-
I believe the index match is supposed to follow this format:
=INDEX({range of cells you want to output}, MATCH([row to match]@row, {range of cells to match to row}, 0))
You wrote it with the beginning of the index as a row, so it can only pull from that row, which is why ABC alone worked. Instead, you should adjust it slightly to be a range:
=INDEX([Capacity 1]:[Capacity 1], MATCH([Collapsing 1]@row, Key:Key, 0))
I hope this helps~
Renée Roberge
-
Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!