Lookup or Dget?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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!D2:D7)


-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
Click to expand...
criteria using lookup but how can I specifiy 2 criteria?
 
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
 
Back
Top