Partial Cell Match to Return Data
Creating a partial cell match in Excel is possible with the intelligent use of formula. The following will seem like an illusion but it is not. This Excel formula will match a word in a string with a lookup table and return a value associated with that word. It is handy if you have a text string with many words and want to group the like words so you can filter like data. The following is an example from the file.
The list of words on the left have a common theme and the lookup table on the right aims to capture the like words and assign a new value so like items can be categorised.
The following is the Excel formula which achieves this task;
=IFERROR(LOOKUP(2^15,SEARCH($G$11:$G$14,B11),$H$11:$H$14),"")
The IFERROR formula is there for protection in case any matches are not met. This occurs when there are no matches in the Lookup list.
The second part of the Excel formula is a Lookup.2^15=32768 the longest cell length allowed is 32767. So this ensures that the second part of the equation will be less than this number. Search looks for the value B11 in all of the cells in the range G11:G14. If the cell is found the same row of the value in H11:H14 is returned.
Attached Excel file is a working example of the above concept.
