Match forst four letters

R

ryguy7272

I am working with a list of names, which includes clients and subsidiaries of
those clients, and the subsidiaries names are always slightly different, but
the first four letters of each name are the same for all of these groups. So
now I have the first four letters of each name, all dollars that each client
has spent, in a pivot table, and now that the names are summed in the pivot
table, I want to extract the full client's name so I can match it with the
total amount spent. I am thinking the function will be something like this:
=IF(NOT(ISERROR(LEFT(MATCH(A5:A250,C5:C250,0),4))),C5:C250,"")

....but this doesn't work. I'd appreciate it if someone could offer some
assistance. If I derive the answer before anyone else I'll post back.


Regards,
Ryan---
 
P

Peo Sjoblom

You would need to put the LEFT function on the range where the lookup/match
is made

=MATCH(A3,LEFT(D3:D6,4),0)

as an example

needs to be array entered

--


Regards,


Peo Sjoblom
 
R

ryguy7272

Thanks Peo, but it didn't work. When I use your function, I get a whole
bunch of #N/As when the client's name is longer than four letters and I get a
'1' when the client's name is four letters or less, like 3M. I am thinking
that I may need to use some version of this:
=IF(C5=LEFT(A5,4),COUNTIF(C$5:C$2004,A5),"")

Any suggestions as to how to modify this type of function?


Thanks,
Ryan---
 
P

Peo Sjoblom

How does C5 and A5 if this is TRUE?

C5=LEFT(A5,4)

so C is only 4 characters?

If so

=IF(C5=LEFT(A5,4),COUNTIF(C$5:C$2004,"="&LEFT(A5,4)),"")



--


Regards,


Peo Sjoblom
 
R

ryguy7272

Thanks for getting back to me. I can't really tell what it is doing now. In
some cases it seems to count the number of items that match my criteria, and
in other cases I can't tell how the final result is derived...even with the
formula audit tool... It may be a circular reference. This is fuzzy logic;
not sure what do do now. Are there are any other ideas out there?

Maybe:
=VLOOKUP(F5,LEFT(A5:C29,4),1)

Or something like:
=VLOOKUP("*"&F5,$A$5:$C$23,1)

....but that's incorrect too...
 
H

Harlan Grove

Peo Sjoblom said:
You would need to put the LEFT function on the range where the
lookup/match is made

=MATCH(A3,LEFT(D3:D6,4),0)
....

If A3 had exactly 4 characters, when would this return something
different than

=MATCH(A3&"*",D3:D6,0)

?

More robust, why not use

=MATCH(LEFT(TRIM(A3),4)&"*",D3:D6,0)

?

These will all return the first match, whether or not that's the match
sought.
 
R

ryguy7272

Ah yes, now I see it. I've done similar things many times in the past...it's
just been a loooonnnng day today...

There are (at least) two solutions:
=INDEX($A$5:$A$1000,MATCH(H8&"*",$A$5:$A$1000,0))

=INDEX($A$5:$A$1000,MATCH(LEFT(TRIM(H8),4)&"*",$A$5:$A$999,0))
(with the second giving the same results as the first because all the spaces
have been trimmed from all the cells already...)


Thanks everyone!!
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top