Forum Discussion
HOW TO: "If cell contains specific text then return specific text"
I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.
But what to use depends on goals, in some cases quick hardcording works quite fine.
If use formatting nested if becomes much more clear and editable. Like this
=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))
If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.
=IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1, IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2, IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3, IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4, IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5, IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6, IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7, IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8, IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9, "No Match" )))))))))I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.
Ah, a classic one.
Key words in column F, returned text in column G, text to check in A1.
=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6)
- Is there an equivalent way to use this Excel logic in Power Query were the source list of text string is a table or another query?
Not sure what exactly you'd like to do, perhaps something like
= Table.SelectRows( #"Table To Filter" , each List.Contains( #"Table With Filter"[ColumnWithValues], [ColumnOnWhichWeFilterSourceTable] ) )SergeiBaklan we have a data cleanliness issue, whereby a freeform field contains key information about each entry. Inside the freeform field are keywords which we want to pull out into a new column. The =LOOKUP(PI(),1/COUNIF(TargetCell,"*"&KeywordRange&"*"),KeywordRange) Formula works well in an Excel sheet, however our dataset is much larger and we will need to handle it in PQ or some other tool. Essentially we want perform the same operation as the above function but within PQ.
I have attached a sample of the dataset, with tables showing the raw data and target keywords (INPUTS) as well as a sample output of cleaned data (OUTPUT). I achieved the example output by using a fuzzy match in PQ, but that is not necessarily a perfect resolution. Perhaps it works.
Cheers,
Hi was googling for a solution to the subject of above thread and found and used your solution,
[Key words in column F, returned text in column G, text to check in A1.
=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6) ]with 1 problem; the keyword found doesn't display.
using Excel 2010
would appreciate any help
thanks!
I have a problemm where I need to filter out the state and district name from the address column and place the same in the adjoining column
State llist can be arranged as a range. But what would be the formula to use and how will it be used to math the address column
Detlef_Lewin This works perfectly in most situations and is super simple, so I want to thank you for providing this! I haven't seen this solution offered anywhere else! The only problem I sometimes run into is that this isn't an exact match, so if I have a list of text that has some of the same terms or letter combinations, it sometimes returns the wrong thing. Is there a way to make this exact match only?
Detlef_Lewin Thank you so much - I spent so long looking for this - it works perfectly!
- Not OP, but this was exactly what I was looking for, thank you!
