Multi-column vlookup

Question

I have the following formula in column B: B

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

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

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

However, I work with over 100k rows and about 40 columns. I do not want to do 100k * 40 * VLOOKUP, I would like to do it only 100k and should not multiply this by all columns. Is there a way (with matrix formulas) to just do VLOOKUP once per row to get all the columns I need?

---

sample data

 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 is faster than VLOOKUP, I would recommend using it. This will reduce the stress that many vlookups will use on your system.

First, find the row that contains what you need in the auxiliary column with MATCH:

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

Then INDEX uses this number, which you can drag and fill all your columns:

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

Your output will 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 would recommend setting these ranges to actually cover the data, rather than referring to the entire column, to increase speed, for example:

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

Answer 2

I was thinking more about your problem, and if you have contorl over the data you are looking for, I have another suggestion that you could try.

In the 'mysheet' where the raw data is stored, add a new column that combines each column into one cell with some kind of unique separator not contained in your data:

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

Then you can do one VLOOKUP or INDEX / MATCH for each row, not 40.

Once you have it on a new sheet, you can separate the results.

---

Separation without formulas

Copy / Paste the results of the search formulas into the Values in the next column.

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

Leave it on Delimited , click Next . Uncheck the tab , check Other and enter your separator (+ in my example).

Click Finish .

---

Formula Separation

Use =FIND() to search for each delimiter and =MID() to pull the text between each set of delimiters, using the previous delimiter as Start_num.

Definitely the more complex of the two methods.

Answer 3

If I understand one thing correctly, what would I do to start using =VLOOKUP(A1;'mySheet'!$A:LastColumn;COLUMN(B1);FALSE) . Thus, the column reference will move as you drag your Vlookup to the right.

Answer 4

There is no formula. There is no conclusion. Thus, there can be no way to apply the formula to only 1 column and move on to others. Another possible way: put the i-formula in 1 cell, use smart $ signs and drag all cells to giffy without the need to add vlookup 40 times.

Vlookup has 4 input codes

1 - output value. Use this $A1 (put $ on A, not 1)

2 - data source data - put $ signs everywhere

3 . Just above all your data in the 1st row add an empty row. Specify the values ​​1 in A1, 2 in B1, 3 in C1, and so on. Now in the formula instead of manually entering "2" or "3" Give a link to these cells. Click $ on Numberal, not in the column ( B$1 ).

4 - enter false or 0

Then drag it everywhere.

Answer 5

• The value of the search. Use this $ A1 (put $ on A, not 1)
• Initial data. Put $ signs everywhere.
• Column Index No. Just use the name of the column from which you want to extract data (for example, COLUMN (B1) if the Lookup value is in column A and you want to get the value from column B).
• Enter false or 0