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?

  • 2
    Your 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. – XOR LX May 21 '15 at 13:20
  • Thanks so much, I didn't expect a solution this easy :) One question, do you know if is an array formula more intensive in this case that its constituent parts? Let's say I had either ={MATCH(SUBSTITUTE(A1," ",""),SUBSTITUTE('Sheet1'!$A:$A8000," ",""),0)} Or 2 columns, =MATCH(SUBSTITUTE(A1," ",""),'Sheet1'!$B1:$B8000,0) and =SUBSTITUTE(A1," ","") If I recalculated the top example would that be any more or less intensive than recalculating both columns in the second? I don't need to know this for any reason, I'm just curious :p Oh, and post your comment as an answer so I can tick it :) – Some_Guy May 22 '15 at 9:00
  • 1
    The two-column version would be more efficient. – XOR LX May 22 '15 at 9:18
  • any links you can point me to to understand why this is? Also don't you want your delicious stackexchange reputation points? :p – Some_Guy May 22 '15 at 12:12
  • 1
    Apologies. I should clarify that the difference in terms of performance will be considerable if, as I assumed (perhaps incorrectly), you were intending to copy the MATCH formula to further rows to obtain equivalent results for other strings. The point being that, in the array version, each instance of that formula is having to calculate afresh a series of 8000 substitutions on the range being queried. In the two-column set-up, these calculations are done just once and then stored. – XOR LX May 22 '15 at 14:18
-1

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

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

| improve this answer | |
  • I think you have misunderstood. This is not going to do what OP intends. MATCH returns a row number or an error. Using SUBSTITUTE on that is going to do a bunch of nothing. – Byron Wall May 21 '15 at 15:08
  • =SUBSTITUTE(OFFSET(Sheet1!A1,MATCH(A1;Sheet1!$A:A;0)-1;0)," ","") Byron – Andrea Fiorito May 21 '15 at 15:34
-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.

| improve this answer | |
  • Please include details how exactly your (or XOR LX) solution looks like. Also, please edit your question and rephrase it a bit, specifically your first paragraph. I wasn't able to understand it 100% – nixda Sep 3 '15 at 15:29
  • Then post the same content as an answer if that user won't do so. Pointing others to comments for the answer isn't considered an answer. – Jamal Sep 3 '15 at 22:05
  • Alright give me a break, just trying to be helpful. I'll format the comments and post tonight – Some_Guy Sep 4 '15 at 11:00
  • So right now, I'd have more reputation by leaving this question unopened rather than posting the solution which solves the problem, great... – Some_Guy Sep 22 '15 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.