lookup mutliple values to return one value
 

I am trying to find a way to lookup more than one value in a row (vlookup) to
return a value in a corresponding row. We have a list of values in column A
(multiple entries of the same value) and a sub-value in column B (call it a
part and a sub assembly in the columns). Each item in column B corresponds
to an added value to the original part (column C). For example, if column A
has two parts, 1 and 2, and 10 different sub-assemblies for each part, there
are 10 rows in column A marked 1, and 10 marked 2. Column B has subassemlies
1-10 for part 1, and 1-10 for part 2. Column C is the added value (for
simplicity, each sub assembly costs a dollar). I want to know how much the
part costs if only the first 5 subassemblies are added to the part (I know,
$5). I have a "work in progress sheet that shows the part and how far along
it is in the assembly (part 1, sub 4), so I would expect to return $4. I
cheated the formula by concatenating the two fields on each of the
spreadsheets and doing the vlookup based on that, but it's not a pretty way
to do it. Any help would be greatly appreciated.

lookup mutliple values to return one value
 

One interp ..

Assuming the data in cols A and B is within A2:B100,
and the inputs are made in:

C1: part# (eg: 1)
D1: sub-assembly (eg: 2)
E1: unit cost per sub-assembly (eg: 1)

we could try in F1:
=IF(OR(C1="",D1="",E1=""),"",E1*SUMPRODUCT(($A$2:$ A$100=C1)*($B$2:$B$100=D1)
))

F1 can be copied down to compute correspondingly for other variations of
inputs in C2:E2, C3:E3, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<David Pelizzari; "IS Manager"
wrote in message
...
I am trying to find a way to lookup more than one value in a row (vlookup)
to
return a value in a corresponding row. We have a list of values in column
A
(multiple entries of the same value) and a sub-value in column B (call it
a
part and a sub assembly in the columns). Each item in column B
corresponds
to an added value to the original part (column C). For example, if column
A
has two parts, 1 and 2, and 10 different sub-assemblies for each part,
there
are 10 rows in column A marked 1, and 10 marked 2. Column B has
subassemlies
1-10 for part 1, and 1-10 for part 2. Column C is the added value (for
simplicity, each sub assembly costs a dollar). I want to know how much
the
part costs if only the first 5 subassemblies are added to the part (I
know,
$5). I have a "work in progress sheet that shows the part and how far
along
it is in the assembly (part 1, sub 4), so I would expect to return $4. I
cheated the formula by concatenating the two fields on each of the
spreadsheets and doing the vlookup based on that, but it's not a pretty
way
to do it. Any help would be greatly appreciated.