VLookup takes too long

[DTTODGG]

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]

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

 

[Pete_UK]

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,lookup_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

 

[DTTODGG]

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]

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()) here:
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