Need help. Need to be able to basically perform 2 vlookups. Here is the
example:

Division Part # Cost/ LB Cost/ingredient
12 1253 5 0.8
12 1246 2 0.3
13 1234 1 0.5
13 1278 3 0.6
14 1278 3 0.6
15 1234 1 0.5

I want to be able to write a formula that finds the division number and for
that division number, find the part number I specify and then give me the
cost per pound or cost per ingredient. Can someone help me please? Thanks.

One generic way,
which returns text data or nums or mixed data ..
and doesn't require array-entering

Assuming source table as posted in A1:D7
Assume the 2 variables are div: 12, part#: 1246

In say, F2, normal ENTER to confirm:
=INDEX(C2:C7,MATCH(1,INDEX((A2:A7=12)*(B2:B7=1246) ,),0))
returns col C [cost/lb]

Just change the index part of it : INDEX(C2:C7,
to: INDEX(D2:D7,
if you want to return the result from col D [cost/ingredient]

Adapt the ranges to suit

Click YES below to thumbs up
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"Wendy" wrote:
Need help. Need to be able to basically perform 2 vlookups. Here is the
example:

Division Part # Cost/ LB Cost/ingredient
12 1253 5 0.8
12 1246 2 0.3
13 1234 1 0.5
13 1278 3 0.6
14 1278 3 0.6
15 1234 1 0.5

I want to be able to write a formula that finds the division number and for
that division number, find the part number I specify and then give me the
cost per pound or cost per ingredient. Can someone help me please? Thanks.

Try this...

Data in the range A2:D7

Use cells to hold your search criteria.

F2 = some division
G2 = some part number

Enter this formula in H2 for the cost/lb:

=SUMPRODUCT(--($A2:$A7=$F2),--($B2:$B7=$G2),C2:C7)

Drag copy across to I2 for the cost/I.

--
Biff
Microsoft Excel MVP


"Wendy" wrote in message
...
Need help. Need to be able to basically perform 2 vlookups. Here is the
example:

Division Part # Cost/ LB Cost/ingredient
12 1253 5 0.8
12 1246 2 0.3
13 1234 1 0.5
13 1278 3 0.6
14 1278 3 0.6
15 1234 1 0.5

I want to be able to write a formula that finds the division number and
for
that division number, find the part number I specify and then give me the
cost per pound or cost per ingredient. Can someone help me please?
Thanks.

On Wed, 18 Feb 2009 16:38:00 -0800, Wendy
wrote:

Need help. Need to be able to basically perform 2 vlookups. Here is the
example:

Division Part # Cost/ LB Cost/ingredient
12 1253 5 0.8
12 1246 2 0.3
13 1234 1 0.5
13 1278 3 0.6
14 1278 3 0.6
15 1234 1 0.5

I want to be able to write a formula that finds the division number and for
that division number, find the part number I specify and then give me the
cost per pound or cost per ingredient. Can someone help me please? Thanks.

Assuming table is in A1:D7 --

Cost/LB
=SUMPRODUCT(($A$2:$A$7=Division)*($B$2:$B$7=PartNu m)*$C$2:$C$7)

Cost/Ingredient
=SUMPRODUCT(($A$2:$A$7=Division)*($B$2:$B$7=PartNu m)*$D$2:$D$7)
--ron