How to create an array that will look up a string value in a sentence and return an int value in Google Sheets

Question

My data is stored in Column F, the raw data looks like this:

The Images- had poor pixel quality.

What I want to do is to create an array formula that will look for my key word which is a string and convert that string keyword value to an int value.

Video= 1
Audio=2
Jpeg=3
Images=4

This is what I tried, but I just can't seem to work out the logic as it doesn't seem to recognize the order of my second array list.

lookup(F2,
       {"Video","Audio","Jpeg","Images"},
       {"1", "2","3","4"}
  ) 

Answer 1

This answer is based on the Docs Editors Help Topic Google Sheet Arrayformula + IF + REGEXMATCH contains text within a range

There are two options:

• a formula using an external list of search strings and corresponding values ("Mapping")
• a formula containing the various search strings and values (no Mapping).

Mapping

=ArrayFormula(IFNA(vLOOKUP(regexextract(A2:A6,TEXTJOIN("|",1,Mapping!A2:A9)),Mapping!A2:B9,2,0),"Other"))

Not Mapped =arrayformula(IF(REGEXMATCH(A2:A6,"Video"),1,IF(REGEXMATCH(A2:A6,"Audio"),2,IF(REGEXMATCH(A2:A6,"Jpeg"),3,IF(REGEXMATCH(A2:A6,"Images"),4,"Other")))))

---

Sample data and results

Mapping