Saturday, November 19, 2011

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

3 comments:

  1. Thanks Yogi, that was exactly what I was looking for. I was wondering if the "query" function could do what I was after, but had no idea how to set it up. I like your blog!

    ReplyDelete
  2. Hi Yogi, I don't see the solution. Please could you check? Many thanks.

    ReplyDelete
  3. Hi Kone:

    Thanks ... I just noticed that the spreadsheet associated with the solution is no longer loading. Let me check it out ... I don't have a back up of the spreadsheet so I may have to redo the solution.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete