vlookups sheet is too big

F

Fish

Group,

I created a sheet a while back with Vlookups all over the
sheet which is currently 14 Megs im trying to get the
sheet down to a reasonable size. Does anybody know of a
way to do this.

Thanks in advance,

Fish
 
D

Dave Peterson

If you're done with the =vlookup()'s, convert them to values.

If you hit ctrl-end and the selected cell is way past where you think it should
be, then visit Debra Dalgleish's site:

http://www.contextures.com/xlfaqApp.html#Unused

And if you've got one key in each row, but use that same key to bring back lots
of columns for that same row, you could dedicate a column for the row to bring
back and use =index() to return the value:

If you have this in D2:F2,

=if(iserror(vlookup(a2,sheet2!A:X,2,false)),"",vlookup(a2,sheet2!A:X,2,false))
=if(iserror(vlookup(a2,sheet2!A:X,3,false)),"",vlookup(a2,sheet2!A:X,3,false))
=if(iserror(vlookup(a2,sheet2!A:X,4,false)),"",vlookup(a2,sheet2!A:X,4,false))

(and maybe more...)

Put in a new column (say D) and use this formula:

=match(a2,sheet2!a:a,0)

Then in one of the former =vlookup() columns:

=if(iserror(d2),"",index(sheet2!B:B,d2))
=if(iserror(d2),"",index(sheet2!C:C,d2))
=if(iserror(d2),"",index(sheet2!D:D,d2))

This way, you're only looking for that match once.
 
Top