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

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Jan 17, 2006.

  1. Guest

    Guest Guest

    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 Sex Weight
    Alex 10 M
    Corey 10 M
    Elvis 9 M


    Worksheet 2
    Name Age Sex 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
     
    Guest, Jan 17, 2006
    #1
    1. Advertisements

  2. Guest

    Biff Guest

    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
    Sex 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 > wrote in message
    news:...
    >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 Sex Weight
    > Alex 10 M
    > Corey 10 M
    > Elvis 9 M
    >
    >
    > Worksheet 2
    > Name Age Sex 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
    >
     
    Biff, Jan 17, 2006
    #2
    1. Advertisements

  3. Guest

    Biff Guest

    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" <> wrote in message
    news:...
    > 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
    > Sex 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 > wrote in message
    > news:...
    >>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 Sex Weight
    >> Alex 10 M
    >> Corey 10 M
    >> Elvis 9 M
    >>
    >>
    >> Worksheet 2
    >> Name Age Sex 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
    >>

    >
    >
     
    Biff, Jan 17, 2006
    #3
  4. Guest

    Peo Sjoblom Guest

    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 > wrote in message
    news:...
    >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 Sex Weight
    > Alex 10 M
    > Corey 10 M
    > Elvis 9 M
    >
    >
    > Worksheet 2
    > Name Age Sex 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
    >
     
    Peo Sjoblom, Jan 17, 2006
    #4
  5. Guest

    Guest Guest

    Thanks both of you!
     
    Guest, Jan 17, 2006
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    How do I specify DGET criteria without using a range?

    Guest, Oct 5, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    1,212
    Guest
    Oct 15, 2004
  2. Guest

    how do i get "DGET" to take the first data meeting the criteria?

    Guest, Jun 7, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    183
    Harlan Grove
    Jun 9, 2005
  3. Eddie A

    How can I get DGET to return all records that meet the criteria?

    Eddie A, Jul 30, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    382
    John C
    Jul 30, 2008
  4. MikeT

    DGET Function - using a split criteria range? (Excel 2007)

    MikeT, Aug 11, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    1,101
    MikeT
    Aug 11, 2008
  5. Chiann Looker

    DGET only limited to 1000 Rows, but I need 8000 rows for DGET funt

    Chiann Looker, Mar 8, 2010, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    228
    Teethless mama
    Mar 9, 2010
Loading...

Share This Page