Appropriate function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hilton - 28 Feb 2007 09:43 GMT

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

Reply to this Message

vezerid - 28 Feb 2007 10:19 GMT

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

> I have a lookup which produced 200 matches (180 unmatched).eg
> G_100-01_LS Match
[quoted text clipped - 16 lines]
> Thanks
> Hilton

Reply to this Message

Philip J Smith - 28 Feb 2007 10:37 GMT

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

> I have a lookup which produced 200 matches (180 unmatched).eg
> G_100-01_LS Match
[quoted text clipped - 16 lines]
> Thanks
> Hilton

Reply to this Message