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

=?Utf-8?B?V2VyZiBOb3J0?=
Guest
Posts: n/a

 17th Jan 2006
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
Guest
Posts: n/a

 17th Jan 2006
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 http://www.pcreview.co.uk/forums/(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
Guest
Posts: n/a

 17th Jan 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
Guest
Posts: n/a

 17th Jan 2006
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 (E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

=?Utf-8?B?V2VyZiBOb3J0?=
Guest
Posts: n/a

 17th Jan 2006
Thanks both of you!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Chiann Looker Microsoft Excel Worksheet Functions 1 9th Mar 2010 03:36 AM MikeT Microsoft Excel Worksheet Functions 0 11th Aug 2008 10:37 PM Eddie A Microsoft Excel Worksheet Functions 1 30th Jul 2008 07:25 AM =?Utf-8?B?QWRlbGxh?= Microsoft Excel Programming 2 21st Nov 2005 08:24 PM =?Utf-8?B?QmFob28=?= Microsoft Excel Worksheet Functions 5 15th Oct 2004 03:43 PM

Features