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