# Excel - Find word within certain lookup reference

October 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.