regexgoogle-sheetsgoogle-sheets-formulavlookuptextjoin
Partial Vlookup not finding value on Google Sheets
I was trying on Google Sheets a very simple VLookup with partial match (using wild card), and for some reason I can't seem to make that work. My formula is rather simple:
=VLOOKUP("*"&A2&"*",$D:$E,2,0)
Where D:E is my lookup table and A2 is the cell to be partially searched. I'm searching for the word "John J" while in my lookup table I have "John" with Id 1. So my VLOOKUP above should return 1.
Here is the Google Spreadsheet with the issue. Any idea what could be the issue?
Solution
you need to do it like this:
=INDEX(IFNA(VLOOKUP(REGEXEXTRACT(A2:A5, TEXTJOIN("|", 1, D2:D)), D:E, 2, 0)))
- Regular expression matching Android package name
- Replace commas not inside single quotes with an @ symbol
- Visual Studio Code Search and Replace with Regular Expressions
- Split string on commas not inside double quotes
- Replace inner letters inside words that start with and end with a specific letter with the same amount of a specific character
- regex for expression with one separator hyphen
- Split address and numbers
- Extracting specific data from a string with regex and Powershell
- Parse datetime string in Ydm.His format
- Extract all words double wrapped in curly braces
- Parse dot-delimited video filename to extract show title, series number and episode number
- Split a string into words, preserving contractions but removing leading apostrophes from words
- Split string on pipes which are not between square braces
- Simple regex pattern for email
- How to extract filename without extension in Makefile
- Matching boot times from log using sed -E
- Regex pattern containing backlashes causes failure in preg_replace()
- preg_replace() pattern starting with < and ending with > doesn't find HTML comments as intended
- Split a string on spaces and/or commas after a word which may be preceded by a small word
- How to convert split() to preg_split()?
- Parse a string without delimiters into a 2d array
- Regex to extract WordPress wp-config database information and automating it with ansible to update a shared hosting environment
- Input regex for UK postcodes constantly invalid
- Parsing a string representation of nested arrays into an Array
- Split a CSV where some entries have double quotes
- A Regex to ensure that a string contains at least two words
- Regex: Match specific characters in any order without more occurrences of each character than specified
- bash + sed: trim trailing zeroes off a floating point number?
- Difference between ?:, ?! and ?=
- Perl search and replace words in file