vlookup with multiple columns

62,429

Solution 1

INDEX works fast than VLOOKUP, I would recommend using that. It'll reduce the strain that many vlookups would put on your system.

First find the row that contains what you need in a helper column with MATCH:

=MATCH(A1,'mySheet'!$A:$A,0)

Then an INDEX using that number, that you can drag across and populate all your columns with:

=INDEX('mySheet'!B:B,$B1)

Your output would be akin to:

ID|Name|Match |Column 1 |Column 2
-------------------------
 1|AB  |Match1|IndexCol1|IndexCol2
 2|CD  |Match2|IndexCol1|IndexCol2
 3|EF  |Match3|IndexCol1|IndexCol2

Also! I'd recomend setting these ranges to actually cover the data, rather than referencing the whole column, for additional speed gains, e.g.:

=INDEX('mySheet'!B1:B100000,$B1)

Solution 2

I was thinking more on your problem, and if you have contorl over the data you're looking up on, I have another suggestion you could try.

In 'mysheet', where the raw data is kept, add in a new column that concatenates each column into one cell, with some sort of unique divider not in your data:

=B1&"+"&C1&"+"&D1&"+"&E1 etc...

Then you could do one VLOOKUP or INDEX/MATCH for each row, instead of 40.

Once you have it in your new sheet, you could split the results back out.


Splitting without formulas

Copy/Paste the results of the lookup formulas as Values in the next column.

Select that column, and in the Data tab on your ribbon, select Text to Columns.

Leave it on Delimited, hit Next. Uncheck Tab, check Other, and input your delimeter (+ in my example).

Click Finish.


Splitting with formulas

Use =FIND() to locate each delimter, and =MID() to pull out the text between each set of delimeters, using the previous delimeter as the Start_num.

Definitely the more complex of the two methods.

Solution 3

If I'm understanding correctly one thing I would do to start would be to use =VLOOKUP(A1;'mySheet'!$A:LastColumn;COLUMN(B1);FALSE). This way your column reference will move as you drag your Vlookup to the right.

Share:
62,429
dan
Author by

dan

Updated on October 05, 2020

Comments

  • dan
    dan about 3 years

    I have the following formula in my B:B column

    =VLOOKUP(A1;'mySheet'!$A:$B;2;FALSE)
    

    It does output in B:B the values found in the mySheet!B:B where A:A = mySheet!A:A. It works fine. Now, I would like to also get the third column. It works if I add the following formula to the whole C:C column:

    =VLOOKUP(A1;'mySheet'!$A:$C;3;FALSE)
    

    However, I'm working with more than 100k lines and about 40 columns. I don't want to do 100k * 40 * VLOOKUP, I would like to only do it 100k and not have to multiply this by all the columns. Is there a way (with array-formulas maybe) to just do the VLOOKUP once per line to get all the columns I need?


    data example

    ID|Name
    -------
     1|AB
     2|CB
     3|DF
     4|EF
    
    ID|Column 1|Column 2
    --------------------
     1|somedata|whatever1
     4|somedate|whatever2
     3|somedaty|whatever3
    

    I would like to get:

    ID|Name|Column 1|Column 2
    -------------------------
     1|AB  |somedata|whatever1
     2|CB  |        |
     3|DF  |somedaty|whatever2
     4|EF  |somedate|whatever3
    
  • SelfTaughtCodingIdiot
    SelfTaughtCodingIdiot over 9 years
    I would also recommend using the INDEX and MATCH formulas together which are faster in my experience and do not care whether the data range is sorted, and does not work in a one way mode like VLOOKUP
  • dan
    dan over 9 years
    Ya I have no problem doing that but that's 40*100000 cells with almost the same formula. It takes about 1 sec per cell to calculate. Have fun waiting forever for the calculations to be done...
  • dan
    dan over 9 years
    This looks really promising on a performance standpoint. Even though that would still make around 40*100000 cells with almost the same formula, the calculation would still be a lot faster than juste 100000 vlookups I guess. I will give this a try.
  • dan
    dan over 9 years
    Actually I only need the MATCH() formula once if I add a MATCH column and then the INDEX are instantly calculated. It works, thanks! Can't say that MATCH+INDEX is really faster than VLOOKUP if we go for only one column, but since this is for multiple columns, it is nearly 100% faster for each columns I add beyond the first.
  • Aaron Contreras
    Aaron Contreras over 9 years
    Awesome, glad that this got you there! I also posted another answer in a different line of thought that may help you, as well :)
  • dan
    dan over 9 years
    Actually, the datas are imported with VBA and then the formulas are dynamically generated with VBA, I just thought it would be faster with formulas than a ton of VBA loops. So I can do pretty much what I want with the data, I just have to match the data from a sheet to another one, but it's more than 100k lines so it all comes down to performances.
  • dan
    dan over 9 years
    When you say it would be better to setting the ranges instead of referencing the whole column, how can I reference it so that it uses the whole column? The number of lines is always changing since data is continuously added.
  • Aaron Contreras
    Aaron Contreras over 9 years
    The same way you already were, e.g. =INDEX('mySheet'!B:B,$B1)