Closed Thread
Results 1 to 16 of 16

vlookup formula not working with data on separate sheet

Hybrid View

  1. #1
    akee
    Guest

    vlookup formula not working with data on separate sheet

    I have a sheet set up with names. I have 2 other sheets with spouses and
    children that belong to the first sheet. I want to insert a formula that
    will look up the correct spouse (based on a member number) and put in it the
    column. Same with the children. I tried the vlookup function, but it did
    not return the correct result. Any suggestions on the best formula? Thank
    you.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    The VLOOKUP() function is the one to use. How exactly are you trying to use it?

  3. #3
    akee
    Guest

    Re: vlookup formula not working with data on separate sheet

    Here is how I have it set up:

    lookup_value = column on main sheet with member numbers
    table_array = corresponding list of member numbers on the spouse sheet
    col_index_num = column on sposue sheet with the spouse's name
    range_lookup = not being used




    "Cutter" wrote:

    >
    > The VLOOKUP() function is the one to use. How exactly are you trying to
    > use it?
    >
    >
    > --
    > Cutter
    > ------------------------------------------------------------------------
    > Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
    > View this thread: http://www.excelforum.com/showthread...hreadid=521875
    >
    >


  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    I was suggesting you provide your exact formula in order to know what ranges you're using, but here's a generic example to work with:

    =VLOOKUP(B3,Sheet2!$A$1:$E$35,3,0)

    The B3 would be your member number (held in cell B3 on Sheet1).
    The Sheet2! would be the name of your sheet holding the spouse info.
    The $A$1:$E$35 would be the table on Sheet2 that holds the info.
    Col A in this example would hold the member numbers.
    The 3 tells Excel to get the info from the 3rd column (in this example it would be Col C).
    The 0 tells Excel to find an exact match for your search value.

  5. #5
    akee
    Guest

    Re: vlookup formula not working with data on separate sheet

    Thank you for your help, that was perfect. My next problem is with the
    dependents. The dependents are on another sheet. The problem is there are
    more than one child per family. How can I pull the children to the master
    sheet as well? I copied the spouse formula and it pulled the first child.
    But I need to get the rest. Here is how the children are set up on the sheet:

    101 Alex Smith
    102 Jamie Dell
    102 Christy Dell
    etc.

    "Cutter" wrote:

    >
    > I was suggesting you provide your exact formula in order to know what
    > ranges you're using, but here's a generic example to work with:
    >
    > =VLOOKUP(B3,Sheet2!$A$1:$E$35,3,0)
    >
    > The B3 would be your member number (held in cell B3 on Sheet1).
    > The Sheet2! would be the name of your sheet holding the spouse info.
    > The $A$1:$E$35 would be the table on Sheet2 that holds the info.
    > Col A in this example would hold the member numbers.
    > The 3 tells Excel to get the info from the 3rd column (in this example
    > it would be Col C).
    > The 0 tells Excel to find an exact match for your search value.
    >
    >
    > --
    > Cutter
    > ------------------------------------------------------------------------
    > Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
    > View this thread: http://www.excelforum.com/showthread...hreadid=521875
    >
    >


  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    It looks like you'll have to go to a complex formula using COUNTIF, OFFSET and MATCH judging by the way you have your setup.

    You could stick with VLOOKUP() by simply switching the additional dependents to adjacent columns instead of adjacent rows.

    How would you like to proceed?

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1