Forum Discussion

Blake385's avatar
Blake385
Copper Contributor
Apr 02, 2022

Interpret tags using a look-up table

I would like to use single letter tags in that cell and then have a formula that interprets those tags based on a look-up table. For example, I might be recording genres and have a look-up table tha...
excel
Formulas and Functions
Starrysky1988's avatar
Starrysky1988
Iron Contributor
Apr 02, 2022

Blake385 

You may write the formula as below.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT("This contains " &

MMULT(SEQUENCE(1,LEN(A1),1,0),--(MID(A1,SEQUENCE(LEN(A1)),1)={"c","h","d"}))&" of "&{"comedy","horror","drama"}&". "),"This contains 0 of comedy. ",""),"This contains 0 of horror. ",""),"This contains 0 of drama. ","")

 

 

  • Blake385's avatar
    Blake385
    Copper Contributor
    Apr 03, 2022
    Thank you very much! Are you able to advise how I could make the formula reference a look-up table so that the 'tags' could easily be changed in future?
    • Starrysky1988's avatar
      Starrysky1988
      Iron Contributor
      Apr 03, 2022

      Blake385 

      Here is the formula with the reference.

      =CONCAT(IF(MMULT(SEQUENCE(1,LEN(A4),1,0),--(MID(A4,SEQUENCE(LEN(A4)),1)=$A$1:$C$1))=0,"",

      "This contains "&MMULT(SEQUENCE(1,LEN(A4),1,0),--(MID(A4,SEQUENCE(LEN(A4)),1)=$A$1:$C$1))&" of "&$A$2:$C$2&". "))

      If you can use LET function, you may shorten the formula as below.

      =LET(A,MMULT(SEQUENCE(1,LEN(A4),1,0),--(MID(A4,SEQUENCE(LEN(A4)),1)=$A$1:$C$1)),CONCAT(IF(A=0,"",

      "This contains "&A&" of "&$A$2:$C$2&". ")))

       

    • Doug_Robbins_Word_MVP's avatar
      Doug_Robbins_Word_MVP
      MVP
      Apr 03, 2022

      Blake385 I would have thought that you could refer to the result of  =VALUETOTEXT(SUBSTITUTE(ARRAYTOTEXT(E1:E3)," ",""))

      in place of "c","h","d", but I am not able to make it work.  Maybe one of the Excel Gurus will come up with a way to use that or something like it.