formula question

[Wendy]

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.

 

[Max]

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

Assuming source table as posted in A17
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(D27,
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

 

[T. Valko]

Try this...

Data in the range A27

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.

 

[Ron Rosenfeld]

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

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

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