If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I have a lookup value of "FOOBAR 123" and I want to return the record called "FOO BAR123"
Ideally, I want to do something like
=MATCH(SUBSTITUTE(A1," ",""),SUBSTITUTE('Sheet1'!$A:A," ",""),0)
SUBSTITUTE on the lookup array breaks it.
It's possible to do
And add a
=SUBSTITUTE(A1," ","") column in Sheet1, but is there a way to avoid having to do this?