SETUP
Say for a simplified example I have a table of raw data from my bank displaying each store I've spent money, and I want to categorize these into either high-street, website, or pub venues.
The way I'm currently tackling this is by having a lookup table where each venue is categorized. The formula for the category column (B3) is simply:
=arrayformula(vlookup($A$3:$A, $D$3:$E$5, 2, false))
However, "TESCO", and "HAVANT TESCO" are the same venue and should have been written as just "TESCO" so the lookup table can work. This is not the case.
I could just add "HAVANT TESCO" to the lookup table, but in the real problem there is a vast number of problematic cells like this that will take a very long time to fix this way. Fixing the issue this way is not feasible.
WHAT I'M LOOKING FOR
What I'd ideally like to use is to be able to put wildcards into the range
of the vlookup
function so anything put around the "TESCO" string will be disregarded. I'd think the formula would look something like:
=arrayformula(vlookup($A$3:$A, "*"&$D$3:$E$5&"*", 2, false))
Using this function just creates an error.
Is it even possible to use wildcards on the range
, or which different formula should I use to achieve the same result?
query contains
but it won't be arrayformula