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.