yogi_Read An Attribute Of An Entity Where Only Part Of It Matches Tabular Value

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user DoctorBean said:
Use VLOOKUP if the reference matches only a part of the search criterion
I'm trying to use VLOOKUP to return a value if a part of the input cell matches the reference table.
I've made an example spreadsheet:
A1: pears
A2: apples
A3: oranges
A4: grapes
B1: 1
B2: 2
B3: 3
B4: 4
Input cell: C1: "I like pears"
https://docs.google.com/spreadsheet/ccc?key=0Aj54xKrDDiaFdGt5bHBQTDhXU1prSktuQ0FvelJpTWc
How can I create a formula that will return a value of "1" because cell C1 contains the word "pears"?
I know how to use VLOOKUP to match an exact value (eg =VLOOKUP("pears" ; A1:B4 ; 2 ; FALSE() ) but I'm not sure how to match it if the input cell contains a text string that matches one of the reference cells.
The application is for a home finance spreadsheet that automatically assigns a category if the transaction name (long string) contains a shorter string that is in a separate list.
----------------------------------

following is one solution to the problem ...