+ Reply to Thread
Results 1 to 5 of 5

How can I use DGET in Excel with criteria that changes each line?

  1. #1
    Werf Nort
    Guest

    How can I use DGET in Excel with criteria that changes each line?

    I want to use Excel to work between two worksheets to pull out a value from
    one and stick it in the other. I tried using DGET but am I having trouble
    because of the Criteria syntax. I want to use the first three columns as
    criteria to fill in the third. Below is a very basic example - I'm talking
    about doing this with thousands of lines of Excel data! I'm not sure if I
    should be using DGET or something else, but it needs to pull the "weight"
    from the second worksheet and put it in the appropriate row in the first
    worksheet . . . PLEASE HELP!

    Worksheet 1
    Name Age *** Weight
    Alex 10 M
    Corey 10 M
    Elvis 9 M


    Worksheet 2
    Name Age *** Weight
    Alex 10 M 100
    Alex 12 M 130
    Brian 11 M 123
    Corey 10 M 98
    Dan 10 M 109
    Elvis 9 M 96


  2. #2
    Biff
    Guest

    Re: How can I use DGET in Excel with criteria that changes each line?

    Hi!

    If there are possible duplicates this might be a problem.

    Assume Sheet1:

    Names in column A, A2:A100
    Age in column B, B2:B100
    *** in column C, C2:C100

    Same setup in Sheet2 and weight is in column D,D2:D100

    Enter this formula in sheet1 D2:

    =SUMPRODUCT(--(Sheet2!A$2:A$100=A$2),--(Sheet2!B$2:B$100=B$2),--(Sheet2!C$2:C$100=C$2),Sheet2!D$2:D$100)

    Copy down as needed.

    Biff

    "Werf Nort" <Werf [email protected]> wrote in message
    news:[email protected]...
    >I want to use Excel to work between two worksheets to pull out a value from
    > one and stick it in the other. I tried using DGET but am I having trouble
    > because of the Criteria syntax. I want to use the first three columns as
    > criteria to fill in the third. Below is a very basic example - I'm
    > talking
    > about doing this with thousands of lines of Excel data! I'm not sure if I
    > should be using DGET or something else, but it needs to pull the "weight"
    > from the second worksheet and put it in the appropriate row in the first
    > worksheet . . . PLEASE HELP!
    >
    > Worksheet 1
    > Name Age *** Weight
    > Alex 10 M
    > Corey 10 M
    > Elvis 9 M
    >
    >
    > Worksheet 2
    > Name Age *** Weight
    > Alex 10 M 100
    > Alex 12 M 130
    > Brian 11 M 123
    > Corey 10 M 98
    > Dan 10 M 109
    > Elvis 9 M 96
    >




  3. #3
    Biff
    Guest

    Re: How can I use DGET in Excel with criteria that changes each line?

    Ooops!

    Better make the criteria row references relative:

    =SUMPRODUCT(--(Sheet2!A$2:A$100=A2),--(Sheet2!B$2:B$100=B2),--(Sheet2!C$2:C$100=C2),Sheet2!D$2:D$100)


    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > Hi!
    >
    > If there are possible duplicates this might be a problem.
    >
    > Assume Sheet1:
    >
    > Names in column A, A2:A100
    > Age in column B, B2:B100
    > *** in column C, C2:C100
    >
    > Same setup in Sheet2 and weight is in column D,D2:D100
    >
    > Enter this formula in sheet1 D2:
    >
    > =SUMPRODUCT(--(Sheet2!A$2:A$100=A$2),--(Sheet2!B$2:B$100=B$2),--(Sheet2!C$2:C$100=C$2),Sheet2!D$2:D$100)
    >
    > Copy down as needed.
    >
    > Biff
    >
    > "Werf Nort" <Werf [email protected]> wrote in message
    > news:[email protected]...
    >>I want to use Excel to work between two worksheets to pull out a value
    >>from
    >> one and stick it in the other. I tried using DGET but am I having
    >> trouble
    >> because of the Criteria syntax. I want to use the first three columns
    >> as
    >> criteria to fill in the third. Below is a very basic example - I'm
    >> talking
    >> about doing this with thousands of lines of Excel data! I'm not sure if
    >> I
    >> should be using DGET or something else, but it needs to pull the "weight"
    >> from the second worksheet and put it in the appropriate row in the first
    >> worksheet . . . PLEASE HELP!
    >>
    >> Worksheet 1
    >> Name Age *** Weight
    >> Alex 10 M
    >> Corey 10 M
    >> Elvis 9 M
    >>
    >>
    >> Worksheet 2
    >> Name Age *** Weight
    >> Alex 10 M 100
    >> Alex 12 M 130
    >> Brian 11 M 123
    >> Corey 10 M 98
    >> Dan 10 M 109
    >> Elvis 9 M 96
    >>

    >
    >




  4. #4
    Peo Sjoblom
    Guest

    Re: How can I use DGET in Excel with criteria that changes each line?

    Don't think DGET is the best way to go,


    =INDEX(Sheet2!$D$4:$D$1000,MATCH(1,(Sheet2!$A$4:$A$1000=A3)*(Sheet2!$B$4:$B$1000=B3)*(Sheet2!$C$4:$C$1000=C3),0))


    where Sheet2A4:D1000 is the database with weight in D4:D1000, names in
    A4:A1000 and so on
    Alex in your example is in A3

    entered with ctrl + shift & enter then copied down will return

    100
    98
    96


    --
    Regards,

    Peo Sjoblom

    Portland, Oregon




    "Werf Nort" <Werf [email protected]> wrote in message
    news:[email protected]...
    >I want to use Excel to work between two worksheets to pull out a value from
    > one and stick it in the other. I tried using DGET but am I having trouble
    > because of the Criteria syntax. I want to use the first three columns as
    > criteria to fill in the third. Below is a very basic example - I'm
    > talking
    > about doing this with thousands of lines of Excel data! I'm not sure if I
    > should be using DGET or something else, but it needs to pull the "weight"
    > from the second worksheet and put it in the appropriate row in the first
    > worksheet . . . PLEASE HELP!
    >
    > Worksheet 1
    > Name Age *** Weight
    > Alex 10 M
    > Corey 10 M
    > Elvis 9 M
    >
    >
    > Worksheet 2
    > Name Age *** Weight
    > Alex 10 M 100
    > Alex 12 M 130
    > Brian 11 M 123
    > Corey 10 M 98
    > Dan 10 M 109
    > Elvis 9 M 96
    >



  5. #5
    Werf Nort
    Guest

    RE: How can I use DGET in Excel with criteria that changes each line?

    Thanks both of you!

+ Reply to 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