Find word within certain lookup reference [Solved/Closed]

Report
-
 gadir_arfan -
Hello,
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"

thank you very much for your help

7 replies

Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi gadir_arfan,

I have no clue what you want to put in C1, C2, C3.

Combine contents of E1 and F1 in C1?
What do you you want to lookup?

Best regards,
Trowa
Hi Trowa,
Thanks for your attention to my problem.
I want to put in "General Cost or Travel Expenses" in C1, C2, C3.
I hope you can help me.
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi gadir_arfan,

What determines what to put in C1, C2, C3?
Is it dependent of the value in column E like (formula in C1)?:
=IF(OR(E1="Electricity",E1="Telephone"),"General Cost",IF(E1="Travel","Travel Expenses",""))

Please clarify why C1 should be General Cost or Travel Expenses.

Best regards,
Trowa
hi trowa,

the logical for C1 :
{ IF in A1 contain word electricity or telephone or travel (etc), vlookup(logical test result,E1:F3,2,false), blank }
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi gadir_arfan,

Now that I fully understand your query, I'm wondering how many different words are (could be) in column A?

If there are not too many you could use column G (or any other column) to indentify the word that is in column A and then do the logical test in column C based on the values in column G.

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.

Best regards,
Trowa
Hi Trowa,

Your formula works well and in line with my expectations. Actually about ten to fifteen different words, maybe I can split the two in which each section will distinguish five or seven different words.

Thank you very much for your help and success for you Trowa.