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

1. Lookup Value. Use this $A1 (put $ on A and not 1)
2. Source data- Put $ signs everywhere
3. Column index no. Just use column name from where data needs to be pulled (e.g. COLUMN(B1) if Lookup value is in Column A and you want value from column B).
4. Type false or 0

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

Answer 3

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)

Answer 4

No formula.No output. So there can't be a way to apply formula on 1 column only and get on the others. The other feasible way is, put i formula in 1 cell, use $ signs inteligently and drag across all cells in a giffy without having to put vlookup 40 times.

Vlookup has 4 codes to input

1-Lookup Value. Use this $A1 (put $ on A and not 1)

2-Source data- Put $ signs everywhere

3-Column index no. Just above your entire data,in the 1st row,add an empty row.Put the values 1 in A1, 2 in B1, 3 in C1 and so on. Now in the formula,instead of manually putting "2" or "3" Give reference to these cells.Put $ on Numberal and not column ( B$1).

4- Type false or 0

Then drag this across everywhere.

Answer 5

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.