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 that records:
| c | comedy |
| h | horror |
| d | drama |
then when I'm making a record of an item, in a cell I write single letter tags for all genres I want and then have a formula to look these up, e.g. (first column manually entered, second column generated through a formula)
| h | This contains horror. |
| ch | This contains comedy. This contains horror. |
| dh | This contains horror. This contains drama. |
Ideally, I would also like this future-proofed in the sense that if I add extra rows to the look-up table, the function does not need to be updated, but will include these rows as well.
Also, ideally I would like it to be able to count occurrences and output something like
| h | This contains 1 of horror. |
| chc | This contains 2 of comedy. This contains 1 of horror. |
| ddhdh | This contains 2 of horror. This contains 3 of drama. |
Thanks for any help!
4 Replies
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. ","")
- 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&". ")))
