VLOOKUP question

[Alexander]

Sheet 2 has the information I need. The only info on Sheet 1 is the employee name. On Sheet 2, column A contains the name; B-E must be associated with the name to get the info to place on Sheet 1. For instance, Sheet 2 has the name Bobby Jones (A), and his age is 45 (B), address is 123 West (C), state is Vermont (D), and # of children is 2 (E). This needs to be transfered to Sheet 1 as the name Bobby Jones (A), age(Q), address (M), state (P), and # of children (T). There is a long list of names (2000) in the HR file (Sheet 2)and also on Sheet 1, but the order of rows is not alpha.

[Brian from Maui]

Assuming sheet 2 has
Column A name
Column B age
Column C address
Column D dependants
Enter in sheet 1 cell B1
=VLOOKUP(A1,SHEET2!RANGE,2,FALSE) FOR AGE
In cell C1
=VLOOKUP(A1,SHEET2!RANGE,3,FALSE) FOR ADDRESS
In cell D1
=VLOOKUP(A1,SHEET2!RANGE,4,FALSE) FOR DEPENDANTS.

If no exact match found will return #N/A

[Aladin Akyurek]

On 2002-04-24 09:35, Alexander wrote:
Sheet 2 has the information I need. The only info on Sheet 1 is the employee name. On Sheet 2, column A contains the name; B-E must be associated with the name to get the info to place on Sheet 1. For instance, Sheet 2 has the name Bobby Jones (A), and his age is 45 (B), address is 123 West (C), state is Vermont (D), and # of children is 2 (E). This needs to be transfered to Sheet 1 as the name Bobby Jones (A), age(Q), address (M), state (P), and # of children (T). There is a long list of names (2000) in the HR file (Sheet 2)and also on Sheet 1, but the order of rows is not alpha.

In Q2 enter:

=IF(COUNTIF(Sheet2!$A$2:$A$2000,A2),VLOOKUP(A2,Sheet2!$A$2:$E$2000,2,0),"Not Found")

where 2 refers to the age column in Sheet2!$A$2:$E$2000 (column B).

You use the same formula in other columns in Sheet1 but you must adjust the third argument of VLOOKUP.

See also the VLOOKUP worksheet function as described in Excel's Help file.

[Alexander]

Thanks. It works!!!