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

[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

 

[Biff]

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

 

[Biff]

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

 

[Peo Sjoblom]

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

 

[Guest]

Thanks both of you!