Hi Everyone,

Part of my job needs me to do Index/Match function constantly, and I ran into the following tricky situation, which hopefully can be solved by all the good people on this forum.

specifically, I need to exactly match two text strings from two Excel sheets. For some items, we have different tail codes to identify their different status.



For example, "abc?" means "abc to be sorted", and "abc-" means "finished abc".

The problem is, the excel won't recognize the difference between "abc?" and "abc-". According to Excel, the two are exactly the same, whoever is the first to be found in a array, got chosen by the Match function.

So when I need to match "abc?", the Match function returns values for "abc-", because it got found in the array first.

Anybody can help?

Thanks a ton in advance,

Jack

Hi and welcome

I think that is because the question mark is used as wildcard in excel.

Instead of looking for "abc?" try ( the character before the question mark is a "tilde" or "swung dash")

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This seems to work

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for replying! This works fine for one material, but the problem is, we have a ton of different materials, probably hundreds, with this kind of coding convention, and they are all in the same array and it would be very time consuming if not impossible to enter the formula you mentioned for each and everyone of them. Is there a work-around formula that can be applied to the whole array without being specific abo
ut what I need to match?

Thanks!

Please post a sample sheet with some examples - Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Please see attached sheet. The green high lighted rows are examples. This is just a partial sheet.

Thanks!

Attached Files

Index_Match_function Problem_test_jackzha.xlsx (78.9 KB, 18 views)