Forum Discussion
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...
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?
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&". ")))