Appropriate function?

H

Hilton

I have a lookup which produced 200 matches (180 unmatched).eg
G_100-01_LS Match
G_101-01_LS Match
G_102-01_LS Match
G_101-01_MN No Match
G_102-01_MN No Match
G_101-01_PV No Match
G_102-01_PV No Match


I would like a function that would allocate
G_101-01_MN, G_102-01_MN
G_101-01_PV, G_102-01_PV

as partial matches based on the G_101-01 and G_102-01 prefixes. Can this be
done?
Also would this function act on the original lookup list or on the resulting
list as above?

Thanks
Hilton
 
V

vezerid

The following *array* formula will return TRUE if "G_101-01" can be
found in A1:A100. It must be committed with Ctrl+Shift+Enter.

=PRODUCT(--ISNUMBER(SEARCH("G_101-01",A1:A100)))>0

Does this help?
Kostis Vezerides
 
P

Philip J Smith

Hi Hilton.

If the last three characters are never relevant then modify your lookup
table so that the index contains only the first 8 characters.

Then in the column to the right of your data (assumed B) enter the following
formula.

=if(isna(match(left(A1,8),LOOKUP,0)),"Not Matched","Matched")

Regards

Phil
 
Top