# 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. ### GuestGuest

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 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

2. ### BiffGuest

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,D2100

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\$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 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. ### BiffGuest

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\$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,D2100
>
> 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\$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 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
4. ### Peo SjoblomGuest

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 Sheet2A41000 is the database with weight in D41000, 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 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
5. ### GuestGuest

Thanks both of you!

Guest, Jan 17, 2006