INDEX and MATCH not returning proper values

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.

Attached is the sheet I'm working on. I've highlighting in orange where the formula breaks down.


excel help.xlsx

L66 does not find the proper value, though. If it did, it should return 198-1209B-26H, but it does not. Instead it returns 198-1209B-1H. I know that M returns the proper last value, because if I change the formula slightly where the formula ends in ,2) to grab from column O it would return the correct value at the end of the matched values. I need this formula in column L to work properly because based on that, I'm going to set up a more complicated formula that is not worth going into, but it requires that condition to be true, and so far it is not.

INDEX($N$4:$P$1423,MATCH(B66&"-"&C66&D66&"-"&E66&F66,$N$4:$N$1423, 1),2) would return the value from column O that I will eventually want to extract, I just have it set the way it is now to confirm it's matching the right row without having to go back and forth.

But the same situation occurs for rows that contain something like 1209A-26H where it will also return 1209A-26H and not 1209A-1H. So I get what you're saying about the sort order, but I'm confused why it will work for 1209A but not 1209B. Also, if I were to just delete any reference to 1209A in columns N down to the start of 1209B and apply the same formula, it will then correctly find 1209B-26H and not link to 1209B-1H.

I've had tunnel vision on this for about a week so maybe I'm misunderstanding you, but again, to me 1209A has the same set up as 1209B and yet it works for that.

So perhaps I am going about this all wrong. Basically I have ID info in columns B:G. I need to find rows in column N that are an EXACT match. Once those rows are isolated, I then need to do a search within those matched rows for certain things, such as values that are one above or one cell below what is in cell "I". In other cases, I need to get the very last possible value in the matched array. I tried my best to make this equation, but it for some reason is not matching the IDs how I need them to match since, as I've already said, 198-1209B-26H is NOT the same as 198-1209B-1H, but based on what I already wrote in the formula, it keeps finding that to be match. Sorry if I'm going in circles here. That's where my brain is at, haha.

My suggestion for better reading your formula in column K:

Formula:

Please Login or Register  to view this content.

Separating the if condition and placing new variables.

I think I found an alternative solution that will work:
XLOOKUP(B68&"-"&C68&D68&"-"&E68&F68, $M$4:$M$1423, $N$4:$N$1423, "try again",0,-1) This will give me the very last value in the matched range.

XLOOKUP(B68&"-"&C68&D68&"-"&E68&F68, $M$4:$M$1423, $N$4:$N$1423, "try again",0,1) This will give me the very first value in the matched range.

Using XLOOKUP does the exact match I was hoping for. I'm still perplexed why the INDEX/MATCH broke for those values since the same circumstance works for the other examples 1209A, 1209C, and 1210A, 1210B with a similar range and order. But oh well.

Thanks for taking your time to help me.