Searching for more than one criteria in lookup

**bern** · 02-20-2005, 05:07 PM

Hi Folks
Using a lookup funtion to obtain the price of a part from a range with parts and price, no problem, but and its a big but can I search for (costing purposes) more than one part or say three different parts in the same search, ie from the same cell to obtain a total cost of parts used.
In anticipation
Bern Great Forum by the way!

**duane** · 02-20-2005, 05:31 PM

=vlookup(part1,range,col)+vlookup(part2,range,col)+.........

**bern** · 02-20-2005, 06:32 PM

Thanks Duane
Not quite what I was looking for, I think I will will have to make separate columns on the same row and insert same formula with different lookup's.

cheers
Bern

**Domenic** · 02-20-2005, 06:33 PM

Here's another approach...

Set up your table of parts and price in Columns A and B.

Sort your table in ascending order. Select both columns and sort by Column A (Parts).

Ensure that the values for the first row in Columns A and B are 0's.

Your table should look something like this...

0 $0
Part1 $10
Part2 $12
Part3 $14
Part4 $16
Part5 $18
Part6 $20
Part7 $22
Part8 $24
Part9 $26

Then dedicate a number of cells in which you will enter your parts of interest. For this example, we'll dedicate cells D1:D5. Therefore, if we enter the following parts in D1:D3...

Part1
Part4
Part6

...the following formula will give you a total cost of $46 for parts used...

=SUMPRODUCT(LOOKUP(D1:D5,A1:B10))

If needed, you could take this one step further by dedicating cells to specify how many parts are used. So, for example, if we dedicate cells E1:E5 to specify how many parts were used and have the following in D1:E3...

Part1 2
Part4 1
Part6 3

...the following formula would give you a total cost of $96 for parts used...

=SUMPRODUCT(E1:E5,LOOKUP(D1:D5,A1:B10))

Hope this helps!

Originally Posted by bern

Hi Folks
Using a lookup funtion to obtain the price of a part from a range with parts and price, no problem, but and its a big but can I search for (costing purposes) more than one part or say three different parts in the same search, ie from the same cell to obtain a total cost of parts used.
In anticipation
Bern Great Forum by the way!