This is close. And it may be what I do instead of using a pivot
table. If I am unable to find a nice way to clean/filter my data I
may choose to use it.

What I am looking for is something more robust and able to utilize a
reference table.

I can export my checking activity in csv format and import into
excel. One of the columns or fields is "Name". The Name identifies
the business where the transaction was completed. Each entry contains
the name of the business plus some other data that identifies each
unique transaction. For example, the data in the column (i.e. D1:D10)
would be something like:

CA SAN JOSE CHEVRON/123456
75483 TRADER JOE 43ASD
CA SACRAM TEXACO/499324
EXXON FG D7832S
WENDY'S 4GY57687
CA SAN FRAN CHEVRON/1D9823
M0 TRADER JOE 434QW
34 WENDY'S 809324DE
565 FRYS 689AHJKIEW
HU7 FRYS EFQFD8923

What I would like to be able to utilize is a reference table (i.e.
B1:C6) similar to the following that I can update over time:

CHEVRON GAS
EXXON GAS
TEXACO GAS
WENDY'S FAST FOOD
TRADER JOE GROCERY
FRYS ELECTRONICS

I would like to be able to place a function in column E that would be
able to look at the information entered in column D, compare it
against the reference table and when it found the substring it would
return the more generic type of purchase. For example, for the first
entry in column C, I would want it to be able to identify the
substring CHEVRON and return GAS. I could then use column E to
conduct analysis with a pivot table and look at our gas, fast food,
grocery, etc purchases. In the current format the data is so unique
that it doesn't lend itself to larger analysis. I have chosen to
utilize or try to find a way to utilize vlookup or match/index because
once I have a reference table established, all I have to do is copy
the function down the column and I am done.