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:
Input cell: C1: "I like pears"
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 ...