#1
|
|||
|
|||
Index Match function
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 |
#2
|
||||
|
||||
Hi and welcome
I think that is because the question mark is used as wildcard in excel. Instead of looking for "abc?" try Code:
=match("abc"&"~?",your_range,0)
__________________
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 |
#3
|
||||
|
||||
This seems to work
Code:
=MATCH("abc"&CHAR(126)&CHAR(63),A1:A5,0)
__________________
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 |
#4
|
|||
|
|||
Quote:
ut what I need to match? Thanks! |
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
Please see attached sheet. The green high lighted rows are examples. This is just a partial sheet.
Thanks! |
Tags |
index/match, text string |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Index, match in Pivottable and Pivotchart | Newbies | Excel | 0 | 11-15-2014 12:58 AM |
Complex Formula using INDEX and Match needed | OTPM | Excel | 5 | 05-23-2013 01:22 AM |
Help with multiple match and index formula | ryanwood | Excel | 1 | 09-12-2012 07:53 AM |
Match Index with sumproduct/vlookup | angie.chang | Excel | 1 | 06-18-2012 08:47 AM |
Moving a Index/Match function | FraserKitchell | Excel | 2 | 01-19-2010 09:38 AM |