Jul 30 2021 08:24 AM
Good morning! I am familiar with Excel certainly, but I have a question. I have a short list of values (frequencies: 1M, 3M, 6M, 1Y, 3Y, 6Y) and then I have another table with thousands of records with a description text field that somewhere in the description will have one of these values. What I want to do is create a column that will pick one of those six values if it is found in the description. So if the description were "FOCO REG INSP - 1Y", the value would be 1Y. So it is like a CASE (or SWITCH) statement in programming.
Thanks for the information
Jul 30 2021 08:40 AM
SolutionThe table on the left is named Frequencies.
The formula in E2 is (confirmed with Ctrl+Shift+Enter to turn it into an array formula):
=IFERROR(INDEX(Frequencies[Frequency],MATCH(TRUE,ISNUMBER(SEARCH(Frequencies[Frequency],[@Description])),0)),"")
Jul 30 2021 08:42 AM
Alternatively, also confirmed with Ctrl+Shift+Enter:
=TEXTJOIN("",TRUE,IF(ISNUMBER(SEARCH(Frequencies[Frequency],[@Description])),Frequencies[Frequency],""))
Jul 30 2021 09:56 AM
The solution I went with is the same as @Hans Vogelaar's alternative formula.
= LET(
isPresent?, ISNUMBER(SEARCH(freq, [@Description])),
freqLocated, IF(isPresent?, freq, ""),
CONCAT(freqLocated))
I used to embed such formulas within a defined Name to avoid CSE but with 365 all that is history.
Jul 30 2021 08:40 AM
SolutionThe table on the left is named Frequencies.
The formula in E2 is (confirmed with Ctrl+Shift+Enter to turn it into an array formula):
=IFERROR(INDEX(Frequencies[Frequency],MATCH(TRUE,ISNUMBER(SEARCH(Frequencies[Frequency],[@Description])),0)),"")