Hi,

I am not sure that I understood your question correctly, but this is my
understanding. Assume that your data is arranged in the following manner.
Assume that the Data below is in range B5:C9

Fruits Amount

Apples 100
Apples and Oranges 200
Oranges 300
Bananas 400

The task is to sum up all instances of Oranges. To accomplish this, enter
Oranges in cell B10 and enter the following array formula (confirmed by
Ctrl+Shift+Enter) in cell C10

SUM(IF(ISNUMBER(FIND(B10,$B$5:$B$8)),$C$5:$C$8))

Please let me know how this works for you.

--
Regards,

Ashish Mathur
www.ashishmathur.com
http://www.linkedin.com/in/excelenthusiasts

wrote in message
...
I've been searching the internet and have yet to find a solution,
though it seems as though I've come close.

I would like to use excel to anaylze my account activity with pivot
tables. To do so I need to do some consolidation and I don't want to
manually filter all of my transactions every month.

During a month or year we frequent certain businesses for certain
purchases. For each business, the activity is identified with their
name and some other characters specific to the purchase. For example,
all our Chevron purchases are of the format *CHEVRON*, Target's are
*TARGET*, Wendy's are *WENDYS*, and local grocer are *LOCALGROCER* and
so on.

Ideally, I would like to make use of the vlookup function where I have
a wildcard/substring in the 1st column of the reference table like
*CHEVRON* and when the function finds a match with the lookup value
such as CA SAN JOSE CHEVRON/897983743 it would return my chosen column
index number value. From what I have seen, I don't think vlookup is
capable of recognizing the substring as a wildcard within the larger
lookup value string. From examples I have seen it looks like it could
do the opposite, meaning the reference table has the larger string and
the lookup value can be a substring with wildcard ("*"&R1&"*") as the
lookup_value.

I have also tried the index/match/find or search approach as well. My
difficulty with this approach is the find or search function. I have
attempted to enter the wildcard column of my reference table as an
array ($A$1:$A$5) as the find_text input; however, once my within_text
exceeds or passes the last row of the reference table, it returns
#VALUE!.

Any suggestions on how I could use the two approaches or other
solutions that would not require manual manipulation.