Excel - Find word within certain lookup reference

August 2017




Issue


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" 

Solution


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


Published by aakai1056. Latest update on January 29, 2012 at 04:27 AM by aakai1056.
This document, titled "Excel - Find word within certain lookup reference ," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).