using vlookup

R

Rick

I have one worksheet with user fist name and last name. I have a
second sheet with users first name and last name and username . I want
to match up the second sheets user name with the first sheets first
\last name and insert it to a new column in the first worksheet i was
trying to use vlookup but could not get it to work. is there another
function to do this. all the date are strings no numbers.

thanks in advance

PD
 
T

T. Valko

Are the first and last names in separate cells on both sheets?

Sheet1
A1 = John
B1 = Smith

Sheet2
Column A = first names
Column B = last names
Column C = user names

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0))

Or, normally entered (not an array):

=LOOKUP(2,1/(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),Sheet2!C1:C10)

Biff
 
R

Rick

Are the first and last names in separate cells on both sheets?

Sheet1
A1 = John
B1 = Smith

Sheet2
Column A = first names
Column B = last names
Column C = user names

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0))

Or, normally entered (not an array):

=LOOKUP(2,1/(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),Sheet2!C1:C10)

Biff








- Show quoted text -
Click to expand...

yes they are in seperate columns, I've did this before years ago and
forgot that array function part. thanks I'll give it a shot.
 
R

Rick

Are the first and last names in separate cells on both sheets?

Sheet1
A1 = John
B1 = Smith

Sheet2
Column A = first names
Column B = last names
Column C = user names

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0))

Or, normally entered (not an array):

=LOOKUP(2,1/(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),Sheet2!C1:C10)

Biff








- Show quoted text -
Click to expand...

Biff, I get N/A when I try your calculation , the result I'm looking
for is to populate the username in sheet1 i.e.column C
with the username from sheet 2 based on matching sheet 2 and sheet 1
firstname and lastname columns.

thanks again
Rick
 
T

T. Valko

Which formula did you use?

The INDEX formula needs to be entered as an array formula. That is, type the
formula, then, instead of hitting ENTER like you normally would you need to
use the key combination of CTRL,SHIFT,ENTER. Hold down both the CTRL key and
the SHIFT key then hit ENTER. When done properly Excel will enclose the
formula in squiggly brackets { }. You *can't* just type these brackets in.
You must use the key combination to produce them. Also, if you ever edit an
array formula it must be re-entered using the key combo.

The LOOKUP formula is not an array formula and can be entered normally by
just hitting ENTER.

Biff
 
R

Rick

Which formula did you use?

The INDEX formula needs to be entered as an array formula. That is, type the
formula, then, instead of hitting ENTER like you normally would you need to
use the key combination of CTRL,SHIFT,ENTER. Hold down both the CTRL key and
the SHIFT key then hit ENTER. When done properly Excel will enclose the
formula in squiggly brackets { }. You *can't* just type these brackets in.
You must use the key combination to produce them. Also, if you ever edit an
array formula it must be re-entered using the key combo.

The LOOKUP formula is not an array formula and can be entered normally by
just hitting ENTER.

Biff








- Show quoted text -
Click to expand...

sorry, I was using the index and after I entered the formula I had to
do the CTRL,SHIFT,ENTER , that populated the field with the user name.
I tried copying that cell down the column, but it copies the result,
not the formula. also, is this formula actually matching the first and
last names from both sheets?

thanks again
Rick
 
R

Rick

Which formula did you use?

The INDEX formula needs to be entered as an array formula. That is, type the
formula, then, instead of hitting ENTER like you normally would you need to
use the key combination of CTRL,SHIFT,ENTER. Hold down both the CTRL key and
the SHIFT key then hit ENTER. When done properly Excel will enclose the
formula in squiggly brackets { }. You *can't* just type these brackets in.
You must use the key combination to produce them. Also, if you ever edit an
array formula it must be re-entered using the key combo.

The LOOKUP formula is not an array formula and can be entered normally by
just hitting ENTER.

Biff








- Show quoted text -
Click to expand...

figured it all out, thanks for all the help
Rick
 
Top