3

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)

But the SUBSTITUTE on the lookup array breaks it.

It's possible to do

=MATCH(SUBSTITUTE(A1," ",""),'Sheet1'!$B:B,0)

And add a =SUBSTITUTE(A1," ","") column in Sheet1, but is there a way to avoid having to do this?

Dave
  • 25,513
Some_Guy
  • 794

2 Answers2

-1

Maybe I misunderstand your question, otherwise it should works for you.

=SUBSTITUTE(MATCH(A1,'Sheet1'!$A:A,0)," ","")

Andrea Fiorito
  • 9
-1

The first formula is fine, provided you commit it as an array formula (i.e. with CTRL+SHIFT+ENTER), though in that case you would be strongly advised not to reference the entirety of column A, otherwise this will result in an extremely resource-heavy formula.

Splitting the process into two columns also makes the calculation less intensive.

={MATCH(SUBSTITUTE(A1," ",""),SUBSTITUTE($A:$A8000," ",""),0)}

would be more intensive than the following two column solution

=SUBSTITUTE(A1," ","")                                   //in column B
=MATCH(SUBSTITUTE(A1," ",""),$B1:$B8000,0)

Because in the first case the substitution on column A must be calculated once for each row, but in the second case the substitution for column A must only be calculated once.

Answered by @XOR LX. Posting this to mark question resolved, but XOR LX deserves the credit.

Some_Guy
  • 794