If cell value contains value from list (wildcard match), retrieve data from a specific column
I am using Google Sheets and have two sheets, Sheet1 and Sheet2. I want to look if a certain cell in Sheet1 contains a value from column A in Sheet2. The match should be done via wildcard *value*. If there is a match, the corresponding value in column B (Sheet 2) should be retrieved.
Sheet2 looks like this: Two columns, column A is what I want to search for and column B is what I want to return if there is a match.
| Search | Return |
|:---------------------|:--------------|
| dog | Dog |
| hound | Dog |
| juice | Beverage |
| cola | Beverage |
Sheet1 looks like this, column A contains names of different things and column B contains a google sheeet formula.
| Name | Type |
|:---------------------|:--------------|
| Coca-Cola | {formula} |
| Nuka-Cola | {formula} |
| Slurp Juice | {formula} |
| Dog | {formula} |
| Cola Juice | {formula} |
| Doggo | {formula} |
| Doge | {formula} |
| American Foxhound | {formula} |
| Mountain Dog | {formula} |
| The Hound | {formula} |
Sheet1 should look like this in the end, see column B.
| Name | Type |
|:---------------------|:--------------|
| Coca-Cola | Beverage |
| Nuka-Cola | Beverage |
| Slurp Juice | Beverage |
| Dog | Dog |
| Cola Juice | Beverage |
| Doggo | Dog |
| Doge | Dog |
| American Foxhound | Dog |
| Mountain Dog | Dog |
| The Hound | Dog |
This is way beyond my google sheet formula skills. Right now I have the following formula in Sheet1, which did something similar before - but dosen't work at all now:
=LOOKUP(2^15;SEARCH(Sheet2!A:A;*A2*);Sheet2!B:B.
If possible, I don't want to use absolute references for this, since I don't know how long Sheet2 is.
try:
=ARRAYFORMULA(IFNA(VLOOKUP(IFNA(REGEXEXTRACT(LOWER(D2:D),
LOWER(TEXTJOIN("|", 1, SORT(A2:A, 1, 0))))), A2:B, 2, 0)))
- R: Efficient way to str_replace_all without recursively replacing conflicting substitutions?
- Extracting specific substrings from a text field in SQL
- Splitting string using preg_split
- preg_split() first uppercase letter only
- Insert space after a lowercase letter only if the next character is uppercase
- sed replace last line matching pattern
- How to replace variables in a formula with their definitions?
- How to count string occurrence in string?
- Remove characters that aren't letters and numbers, replace space with a hyphen
- Allow only [a-z][A-Z][0-9] and some symbols in a string
- Python argparse regex
- Regex: split string into sets of chars from a pool
- Java String split by empty lines including empty lines at the end
- Extract sub-string within larger block
- Using RegEx how do I remove the trailing zeros from a decimal number
- Using PHP to scrape image url from twitter page
- Regular expression negative lookahead
- Using arrayformula to add suffix, but would like to skip empty cells
- NPM Version Range Grammar not regular?
- Validating IPv4 addresses with regexp
- Regular expression for URL validation (in JavaScript)
- C# - Regex for file paths e.g. C:\test\test.exe
- Remove Annotations from typing.Annotated
- Find files that does not contain a string
- change column names using a dbt macro
- Need help parsing Cisco output
- Move Text Following Comma-Separated Numbers to a New Line
- Nested named regex groups: how to maintain the nested structure in match result?
- Performance optimized approach to mask sensitive data in XML in Java
- How do you use a variable in a regular expression?