VLOOKUP question

Alexander

New Member
Joined
Apr 15, 2002
Messages
45
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top