Vlookup over different worksheets

  • Thread starter Thread starter kwing
  • Start date Start date
K

kwing

Hi All,

I'm trying to do a vlookup from one worksheet to another. How do I
specify the right column?

My formula so far is
=VLOOKUP(D2,'Quad comparson lookup table'!A2:F97,'Quad comparson lookup
table'!'Quad comparson lookup table'!COL2,FALSE)
but I know that the 'COL2' text is incorrect, can anyone help me out?

Cheers,
Kirsty
 
Kirsty,

I don't have a direct answer for you; Excel has little across-worksheet
functionality. But often data is in multiple worksheets that really should
be in one (and easily separated for presentation or calculations with things
like Autofilter). Take a look at "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm. I earnestly recommend you
consider this. Don't be too quick to dismiss it.
 
Hi!

If I understand your question:

=VLOOKUP(D2,'Quad comparson lookup table'!A2:F97,2,0)

Searches for the lookup value in Quad comparson lookup table A2:A97 and if
found returns the corresponding value from Quad comparson lookup table
B2:B97.

Tip: long sheet names are a real PITA!

Biff
 
Kirsty,

Oops. I think I misread your question. It appears you don't have multiple
tables to look up in after all. Sorry.

I think Biff's answer is what you need, if you want it to retrieve data from
column B.
 
Thanks guys,

I'll give Biffs suggestion a go. The reason I haven't got the lookup
table in the same sheet is because I regularly sort and resort
worksheet contents and it's a pain having to select all except for the
lookup table rather than just using ctrl+A - but if anyone has a
suggestion round that then it would be appreciated - e.g can I section
off worksheet so it doesn't sort the lookup table rows at the bottom??
 
kwing,

I didn't mean you should have the lookup table in the same sheet. As for
sectioning off a part of the table, just change the range that the LOOKUP is
looking at. A defined name (Insert - Name - Define) can be useful for that.
 
Back
Top