CODEWITHSUNDEEP
google-sheetswildcardvlookup
Ricky Adams
Ricky Adams

Reputation: 175

Reverse Wild Card Vlookup

I'm trying to perform to me what seems like a reverse wildcard lookup, but if anyone has suggestions on a better formula I'm totally open to that.

I have a column that has multiple words in the cell ex:(the house was brown), and then I have a table that has a column of colors and a column of values ex:(A2=brown, B2=1738) and what I would like to do is run a vlookup off (the house is brown) and return with the 1738 value.

In my mind the formula looks like this 

=vlookup("the house was brown",sheet2!"*"&A:B&"*",2,false)

Where the wild cards are attached to the lookup values, but that doesn't seem to work and I've played around with the placement of the wildcards and have turned up with nothing.

Any help would be much appreciated.

Thanks!

Upvotes: 0

Views: 797

Answers (1)

Paul
Paul

Reputation: 897

From the question, you have data like:

Data in A1:A4
The mouse is brown
The house is brown
The car is red
The robin is red

supplemented by a lookup table of the form

Named Range "LookupTable"
Col1 ; Col2
brown; 5
red; 6
blue; 7
etc.

Then the formula, 

=trim(join(" ",arrayformula(iferror(vlookup((iferror(regexextract(lower(A1),"\b"&LookupTableCol1&"\b"),"")),LookupTable,2,FALSE),""))))

in B1:B4, where A1 is the top-right of the LookupTable, gives the output

Output:
5
5 6
6
6

That is, every instance of the word of the lookup table is identified by the associated number, and those numbers are listed (space separated) one at a time in a single cell.

Note that the lookup table must be in lower case to get positive matches.

Is that what you were after?

For the sake of editing, testing, or refining the answer here is a link to a spreadsheet showing this in action which I'll remove once the answer's accepted.

Clarification of how this works
This works by

(1) Using by using a regular expression with a string literal to identify the presence of one of the lookup words in LookupTableCol1. The "\b" part added to each end ensures that it is matched as a whole word, and the lower() function is used to ensure our results are not case-sensitive.
(2) This is then inputted into vlookup() to search for that word in the lookup table and return the associated value.
(3) An "iferror()" is used to remove any "N/A" errors if no matches are found in the regexextract() or the vlookup().
(4) Arrayformula() is used to repeat these actions down LookupTableCol1 and return an array (5) Join() is used to concatenate the whole array into a single cell for readability.
(6) Trim() is used to remove any leading or trailing spaces resulting from blank cells in the array returned from arrayformula as a result of using join() with an array containing blank cells.

Upvotes: 4

Related Questions