Text formula

[Guest]

Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

 

[T. Valko]

With this in Sheet1 A2:A5

PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

With this in Sheet2 A2:A5

PCM AAAA AAAA AAAAA
AAA CMT AAAAA
AAAA GROUP NETWORK
ttttttt

Enter this formula in Sheet2 C2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ISNA(MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)),"other",INDEX(Sheet1!A$2:A$5,MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)))

Copy down as needed.

Biff

 

[Peo Sjoblom]

You can use wildcards

=VLOOKUP("*PCM*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*PCM*",B2:B300)


as an example

 

[Guest]

T. Valko you are genious!!! it's work! Thanks
Eva

 

[Guest]

Hi Peo
Thanks for your help. You formula is good, but I would have to each time
input the value I am looking for, but still, I like the formula and I am
going to use it for the other purpose I am working on right now.
Thanks
Eva

 

[Dave Peterson]

You can still put the value to look for in another cell:

 

[Guest]

Hi
Actual you are right, I didn't think about this option
thanks
Eva