Help: INDEX, MATCH not working

[sndameron]

I have a bit of a conundrum. I am trying to match a concatenation of cells with that in another column. This concatenation essentially equals "198-1209B-26H", for example. When looking in column N, I want to find the cells that match that concatenation. In this example, starting at row 66, that means the matched values should then match to the following range N545:N562. I then want to extract the value that is in the very most top of that range that I place in column L. In another cell, column M, I want to also extract the value that is in the very last row of that matched range. As you can see, in column M, it works fine. But in column L, the very top value is matching with cells otuside of the match range. This makes zero sense. The same formula works for the whoel dataset except for anything that starts with 198-1209B; it works fine for anything with the start of 198-1209A, 198-1209C, 198-1210A, 198-1210B. I've been going in circles. If I delete the first few rows in N that start with 198-1209B-1H then it returns fine.

The data file has too many rows and columns, 1639x16 to post here. So if anyone could be so kind to help me, I can email the file. Or know of a way to post it here, that'd be great!

 

[sndameron]

Here's a screenshot

I'm using the formula in L66 to get top-most: INDEX($N$4:$P$1423,MATCH(B66&"-"&C66&D66&"-"&E66&F66,$N$4:$N$1423, 1),1)
to get bottom-most of a range, in cell M66: INDEX($N$4:$P$1423,MATCH(B66&"-"&C66&D66&"-"&E66&F66,$N$4:$N$1423, 0),1)

 

[Peter_SSs]

• I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The data file has too many rows and columns, 1639x16 to post here.

• So make up a small one with dummy data but illustrates the concept and requirement and post that?

I can email the file.

• Sorry, that is not permitted under the Forum Rules

Or know of a way to post it here,

• I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

 

[sndameron]

I can't post a smaller version. When I do that, then everything appears to work as it should be. But I think I found a workaround using XLOOKUP instead of INDEX/MATCH