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---


--
RyGuy

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

"ryguy7272" wrote in message
...
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---


--
RyGuy

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---

--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---


--
RyGuy

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

"ryguy7272" wrote in message
...
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---

--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---


--
RyGuy

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...




--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---

--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---


--
RyGuy

Can you post some sample data that demonstrates what you're trying to do?

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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...




--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---

--
RyGuy


"Peo Sjoblom" wrote:

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

"ryguy7272" wrote in message
...
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---


--
RyGuy

"Peo Sjoblom" wrote...
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.

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---

--
RyGuy


"Harlan Grove" wrote:

"Peo Sjoblom" wrote...
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.