Index Match on same sheet only matches to the first key found at top of row

cabbsman

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?

.

rrenee

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~

Carson Penticuff

You need to specify the entire Capacity 1 column.

=INDEX([Capacity 1]:[Capacity 1], MATCH([Collapsing 1]@row, Key:Key, 0))

rrenee

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~

cabbsman

Thank you so much!