View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
~L ~L is offline
external usenet poster
 
Posts: 177
Default Vlookup between two sheets

Yes, you can.

To make this less confusing (since A and B are the name of both sheets and
columns) workseet A is now worksheet One and worksheet B is now worksheet Two.

The syntax for referencing another sheet's range is:
OtherSheetName!A1:A50 (or whatever range you like).

If the other sheet's name has spaces:
'Other Sheet Name'!A1:A50

In VLOOKUP this looks like:

=VLOOKUP(Two!A1,One!$A$1:$A$50,2)

Or, more generally:

=VLOOKUP(Lookup Value, Lookup Range, Column Number)

It is almost always the case that you want to include the $ signs on your
lookup range to prevent that range from updating when copied or dragged.

With VLOOKUP, the lookup range must be in the first column of the lookup
range. If this is not the case, rearrange the data or use INDEX with MATCH
like so:

=INDEX(One!$A$1:$A$50,MATCH(Two!A1,One!$B$1:$B$50, 0))

and the general form:

=INDEX(Return Range,MATCH(Lookup Value, Lookup Range, 0))

"chris" wrote:

I have two worksheets, worksheet 'A' has two columns of data, worksheet 'B'
has only one. I would like to use vlookup to search the columns on worksheet
'A' using the data from 'B' and enter the corresponding data from the second
column on 'A' into 'B'. Is this possible?