formula question

  • Thread starter Thread starter Wendy
  • Start date Start date
W

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.
 
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
 
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.
 
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.
Click to expand...

Assuming table is in A1:D7 --

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
 
Back
Top