SOLVED

Table of values found in another table

Copper Contributor

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

3 Replies
best response confirmed by guyinkalamazoo (Copper Contributor)
Solution

@guyinkalamazoo 

The table on the left is named Frequencies.

S0631.png

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)),"")

@guyinkalamazoo 

Alternatively, also confirmed with Ctrl+Shift+Enter:

 

=TEXTJOIN("",TRUE,IF(ISNUMBER(SEARCH(Frequencies[Frequency],[@Description])),Frequencies[Frequency],""))

@guyinkalamazoo 

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.

1 best response

Accepted Solutions
best response confirmed by guyinkalamazoo (Copper Contributor)
Solution

@guyinkalamazoo 

The table on the left is named Frequencies.

S0631.png

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)),"")

View solution in original post