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?

Improve this question
5
  • 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, 2015 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, 2015 at 9:00
  • 1
    The two-column version would be more efficient.
    – XOR LX
    May 22, 2015 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, 2015 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, 2015 at 14:18

2 Answers 2

Reset to default
-1

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

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

Improve this answer
2
  • 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, 2015 at 15:08
  • =SUBSTITUTE(OFFSET(Sheet1!A1,MATCH(A1;Sheet1!$A:A;0)-1;0)," ","") Byron
    – Andrea Fiorito
    May 21, 2015 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
4
  • 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, 2015 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, 2015 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, 2015 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, 2015 at 11:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .