vlookup with multiple columns

Question

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

Answer 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)