Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup takes too long
Hello-
I have in the past done VLOOKUP on multiple columns to retrieve data from other spreadsheet. This process takes forever and a day when the files are huge. Is there a quicker technique? Can I do a VLOOKUP on one column and then, when found use some other quicker function (maybe MATCH or INDEX - I don't know how to use these) to get the other columns? Sample (of 6000 line spreadsheet) ColA ColB ColC ColD ColE Data Data VLookUP VLookup VLookup ColC = VLOOKUP (A2,Validdata, 14, false) ColD = VLOOKUP (A2, Validdata, 15, false) ColE = VLOOKUP (A2, Validdata, 17,false) etc... Thank you so much for all your shared wisdom! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup takes too long
I'd insert a helper column that only returns the index into the first column of
that lookup table. For instance, I'd insert this into C2: =match(a2,sheet2!a:a,0) Then rely on what that returns to return the values that I want. =if(isna(c2),"",index(sheet2!b:b,c2)) =if(isna(c2),"",index(sheet2!C:c,c2)) =if(isna(c2),"",index(sheet2!d:d,c2)) .... DTTODGG wrote: Hello- I have in the past done VLOOKUP on multiple columns to retrieve data from other spreadsheet. This process takes forever and a day when the files are huge. Is there a quicker technique? Can I do a VLOOKUP on one column and then, when found use some other quicker function (maybe MATCH or INDEX - I don't know how to use these) to get the other columns? Sample (of 6000 line spreadsheet) ColA ColB ColC ColD ColE Data Data VLookUP VLookup VLookup ColC = VLOOKUP (A2,Validdata, 14, false) ColD = VLOOKUP (A2, Validdata, 15, false) ColE = VLOOKUP (A2, Validdata, 17,false) etc... Thank you so much for all your shared wisdom! -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup takes too long
I think you will find it quicker still if you use MATCH once to
determine if the sought item exists in the lookup table and then use an INDEX formula to bring the values from the other columns. Essentially, you would have a new column C (which you can hide afterwords) with something like: =IF(ISNA(MATCH(A2,lookup_data,0)),"",MATCH(A2,look up_data,0)) where lookup_data is just the first column of your Validdata table. Then in column D you can have something like: =IF($C2="","",INDEX(Validdata,$C2,14)) and copy this across changing 14 to 15, 16 etc, or even put it in as: =IF($C2="","",INDEX(Validdata,$C2,COLUMN(N1))) so that when you copy this across the COLUMN(N1) (which returns 14) will become COLUMN(O1), COLUMN(P1) etc, which return 15, 16 etc automatically. This should be a lot faster. Hope this helps. Pete On Mar 20, 1:34*pm, DTTODGG wrote: Hello- I have in the past done VLOOKUP on multiple columns to retrieve data from other spreadsheet. This process takes forever and a day when the files are huge. Is there a quicker technique? Can I do a VLOOKUP on one column and then, when found use some other quicker function (maybe MATCH or INDEX - I don't know how to use these) to get the other columns? Sample (of 6000 line spreadsheet) ColA * ColB * ColC * * * *ColD * * * ColE Data * Data * VLookUP *VLookup *VLookup ColC = VLOOKUP (A2,Validdata, 14, false) ColD = VLOOKUP (A2, Validdata, 15, false) ColE = VLOOKUP (A2, Validdata, 17,false) etc... Thank you so much for all your shared wisdom! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup takes too long
Thank you Dave, I will try your suggestion.
I would like to know - When to use VLOOKUP When to use MATCH and INDEX I've only used VLOOKUPs Thanks in advance. "Dave Peterson" wrote: I'd insert a helper column that only returns the index into the first column of that lookup table. For instance, I'd insert this into C2: =match(a2,sheet2!a:a,0) Then rely on what that returns to return the values that I want. =if(isna(c2),"",index(sheet2!b:b,c2)) =if(isna(c2),"",index(sheet2!C:c,c2)) =if(isna(c2),"",index(sheet2!d:d,c2)) .... DTTODGG wrote: Hello- I have in the past done VLOOKUP on multiple columns to retrieve data from other spreadsheet. This process takes forever and a day when the files are huge. Is there a quicker technique? Can I do a VLOOKUP on one column and then, when found use some other quicker function (maybe MATCH or INDEX - I don't know how to use these) to get the other columns? Sample (of 6000 line spreadsheet) ColA ColB ColC ColD ColE Data Data VLookUP VLookup VLookup ColC = VLOOKUP (A2,Validdata, 14, false) ColD = VLOOKUP (A2, Validdata, 15, false) ColE = VLOOKUP (A2, Validdata, 17,false) etc... Thank you so much for all your shared wisdom! -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup takes too long
If the lookup table has the key matching column to the far left, then =vlookup()
or =index(match()) will work. If the key matching column isn't the leftmost column of the lookup table, then =index(match()) is the obvious choice. If you're bringing back the second column of the lookup range and you're not using tons of formulas, then using =vlookup() seems like a reasonable approach. But if you're bringing back lots of different columns via lots of formulas, then that dedicated "match" column and a bunch of =index()'s seems reasonable. If you've got a giant table A1:IV9999 and want to bring back stuff from column IV, then this formula: =vlookup(a1,sheet2!a1:iv9999,256,false) might be a problem. If you do lots of changes to the table--but just in columns B:IU, then that formula is going to recalc with each change (assuming automatic recalc). =index(sheet2!iv1:iv9999,match(a1,sheet2!a1:a9999, 0)) would seem like a better approach. This formula would only recalc if A1:A9999 or IV1:IV9999 were changed. =========== But I think the real problem comes in when you have lots and lots of formulas and the table is large. I know that if I wanted to fill 10000 rows by 50 columns with =vlookup() or =index(match()) formulas, things would slow down to a crawl. I'd fill 100 rows at at time. Let excel calculate. Convert the first 99 rows to values. Then drag the single formula down another 100 rows. (Ok, sometimes, I'd drag down lots of rows to see if I locked up excel!). I think most of this comes from just using it (over and over and over). ========== Debra Dalgleish has lots of notes on =vlookup() and =index(match()) he http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble DTTODGG wrote: Thank you Dave, I will try your suggestion. I would like to know - When to use VLOOKUP When to use MATCH and INDEX I've only used VLOOKUPs Thanks in advance. "Dave Peterson" wrote: I'd insert a helper column that only returns the index into the first column of that lookup table. For instance, I'd insert this into C2: =match(a2,sheet2!a:a,0) Then rely on what that returns to return the values that I want. =if(isna(c2),"",index(sheet2!b:b,c2)) =if(isna(c2),"",index(sheet2!C:c,c2)) =if(isna(c2),"",index(sheet2!d:d,c2)) .... DTTODGG wrote: Hello- I have in the past done VLOOKUP on multiple columns to retrieve data from other spreadsheet. This process takes forever and a day when the files are huge. Is there a quicker technique? Can I do a VLOOKUP on one column and then, when found use some other quicker function (maybe MATCH or INDEX - I don't know how to use these) to get the other columns? Sample (of 6000 line spreadsheet) ColA ColB ColC ColD ColE Data Data VLookUP VLookup VLookup ColC = VLOOKUP (A2,Validdata, 14, false) ColD = VLOOKUP (A2, Validdata, 15, false) ColE = VLOOKUP (A2, Validdata, 17,false) etc... Thank you so much for all your shared wisdom! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting takes too long | Excel Worksheet Functions | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
File takes too long to open | Excel Discussion (Misc queries) | |||
Save takes long time | Excel Discussion (Misc queries) | |||
Recalculation takes too long - help!!!! | Excel Worksheet Functions |