vlookup with multiple columns
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.
dan
Updated on October 05, 2020Comments
-
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 over 9 yearsI 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 over 9 yearsYa 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 over 9 yearsThis 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 over 9 yearsActually 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 over 9 yearsAwesome, glad that this got you there! I also posted another answer in a different line of thought that may help you, as well :)
-
dan over 9 yearsActually, 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 over 9 yearsWhen 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 over 9 yearsThe same way you already were, e.g. =INDEX('mySheet'!B:B,$B1)