Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VLOOKUP question

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,074
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. It works!!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •