PC Review


Reply
Thread Tools Rate Thread

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

 
Reply With Quote
 
 
 
 
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 (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 . . . 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
>



 
Reply With Quote
 
 
 
 
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 . . . 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
>>

>
>



 
Reply With Quote
 
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 . . . 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
>


 
Reply With Quote
 
=?Utf-8?B?V2VyZiBOb3J0?=
Guest
Posts: n/a
 
      17th Jan 2006
Thanks both of you!
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DGET only limited to 1000 Rows, but I need 8000 rows for DGET funt Chiann Looker Microsoft Excel Worksheet Functions 1 9th Mar 2010 03:36 AM
DGET Function - using a split criteria range? (Excel 2007) MikeT Microsoft Excel Worksheet Functions 0 11th Aug 2008 10:37 PM
How can I get DGET to return all records that meet the criteria? Eddie A Microsoft Excel Worksheet Functions 1 30th Jul 2008 07:25 AM
Use index to get range for dget to use =?Utf-8?B?QWRlbGxh?= Microsoft Excel Programming 2 21st Nov 2005 08:24 PM
How do I specify DGET criteria without using a range? =?Utf-8?B?QmFob28=?= Microsoft Excel Worksheet Functions 5 15th Oct 2004 03:43 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:33 AM.