Excel - Find word within certain lookup reference

October 2016


I need help to solve this problem.
Example :
Cell A1 : "cost of electricity" Cell B1 : "$100"  
Cell A2 : "telephone charges" Cell B2 : "$75"  
Cell A3 : "travel cost" Cell B3 : "$150" 

I want to classify in Cell C1, C2, C3 with certain lookup reference as:
Cell E1 : Electricity Cell F1 : "General Cost"  
Cell E2 : Telephone Cell F2 : "General Cost"  
Cell E3 : Travel Cell F3 : "Travel Expenses" 


Put this formula in column G:
=IF(ISERROR(SEARCH("electricity",A1)),IF(ISERROR(SEARCH("telephone",A1)),IF(ISERROR(SEARCH("travel",A1)),"No results",3),2),1)  

Put this formula in column C:
=IF(G1="No results","No results",VLOOKUP(IF(G1=1,"electricity",IF(G1=2,"telephone",IF(G1=3,"travel",""))),$E$1:$F$3,2,FALSE)) 

Keep in mind that you can hide column G or make its text color white to keep your sheet clean.

Thanks to TrowaD for this tip.

Related :

This document entitled « Excel - Find word within certain lookup reference  » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.