Lookup or Dget?

[Guest]

My database is
Factory NAME Dept Valu
1000 Apples 1 5
1000 Apples 2 11
1010 Pears 1 7
1010 Pears 2 8
1020 Plums 1 9
1020 Plums 2 10

On a separate sheet I would like to produce a table
Factory Dept Balanc
1000 1 5
1010 1 7
1020 1 9

I can get the top value using dget and look up a single criteria using lookup but how can I specifiy 2 criteria

Apologies if this has been asked and thanks..

Bo

 

[just_jon]

Assuming your posted data is in columns A-C, then you can do a multi-ke
sum via --

=IF(LEN(A2),SUMPRODUCT(--('Databas
Sheet'!$A$2:$A$1000=A2),--('Database Sheet'!$B$2:$B$1000=B2),'Databas
Sheet'!$C$2:$C$1000),"")

where A2 holds a value like 1000, B2 like 1 and the reasonable end o
data on Database Sheet is row 1000.

Rename Database Sheet to your actual sheet name, and copy down as fa
as needed

 

[Jack]

Bob,

Assuming your data begins in cell A1 on both sheets, here
is the formula you would need:

On sheet 2, in the "Balance" column (assuming you already
have values in place for "Factory" and "Dept"):

=SumProduct((Sheet1!A2:A7=Sheet2!A2)*(Sheet1!C2:C7=Sheet2!
B2)*Sheet1!D27)


-Jack

-----Original Message-----
My database is:
Factory NAME Dept Value
1000 Apples 1 55
1000 Apples 2 110
1010 Pears 1 76
1010 Pears 2 83
1020 Plums 1 99
1020 Plums 2 102

On a separate sheet I would like to produce a table:
Factory Dept Balance
1000 1 55
1010 1 76
1020 1 99

I can get the top value using dget and look up a single

criteria using lookup but how can I specifiy 2 criteria?

 

[Frank Kabel]

Hi Bob
try the following array entered formula (entered with CTRL+SHIFT+ENTER)
to get the Value
=INDEX('sheet1'!$D$1:$D$100,MATCH(A1&B1,'sheet1'!$A$1:$A$100 &
'sheet1'!$C$1:$C$100,0))


Assumptions:
- sheet 1 stores your 'database' in columns A, B, C, D
- on sheet2 column a and col. b store the lookup values for Factory and
Department

 

[JE McGimpsey]

I'm not sure what your second criterion is - it looks like your second
table is just all Dept 1.

You can use advanced filter to pull the data into a second sheet:

http://contextures.com/xladvfilter01.html#ExtractWs