Revised Query: Suggest some solution

[Murtaza]

Dear Experts,

Consider the following example & provide any best possible formula.

In a Working File:
---------------------------------------
Column A Column B
---------------------------------------
1 Product Name Sales_Units
2 Product "a" 2220
3 Product "b" 5175
4 Product "c" 395
5 Total Value ?? ==> This is cell 'B5'

To get the Total Value, the price for above products are listed in a seprate
sheet "Pricelist", among other 100s of products.

Pricelist:
---------------------------------------
Column A Column B
---------------------------------------
1 Product Name UnitPrice
2 Product "a" 20
3 Product "g" 75
4 Product "c" 95
5 Product "t" 15
6 Product "b" 25
6 etc.... etc....

The required formula or method have to find each product i.e. Product "a",
"b", "c" in "Pricelist" & then multiply its "Sales_Units" by its "UnitPrice"
and then give total value of all products in cell "B5".

I am expecting an answer in cell 'B5' is 211,300

Kindly note that this formula or method should be a global one i.e. I can
use this formula on other sheets or file.

Any Suggestions

Thanks for your understanding!
Murtaza

 

[Don Guillett]

Won't a simple vlookup work?
=b2*vlookup(b1, pricelist!$B$2:$f$2000,2,false)+next product

 

[A.W.J. Ales]

Murtaza,

Assuming in column A of your working sheet contains only the a ; b ; c etc.
en also assuming your lookup range contains the same values (i.e. also a; b;
c etc.) in the first column and the prices in the second column, you could
use the formula (in cell A5 of your working sheet) :

=SUM(TRANSPOSE(VLOOKUP($A2:$A4; Lookup Range;2;0))*($B$2:$B$4))

You must however entre ths formula in both A5 and A6 AND you must array
enter it ( i.e. select both cells, type in the formula and hit Control;
Shift Enter together.; your formula is than surrounded bij { } . You can
NOT manually enter those { } yourself .)

Both cells will show the value 211300. To suppres the value in A6 format
the cell with textcolor equal to the background color.

I must admit that I don't (yet) know what exactly is going on. Array
entering the formula in one cell only gives a wrong result (which i by the
way couldn't yet explain). Testing with an other function than Vlookup
( in fact with the power function) gave a correct result ( if inputted in
one cell).
Testing on the Vlookup function ( array entering VLOOKUP($A2:$A4; Lookup
Range;2;0) in just one cell with the Rows or the Columns function around it
gave as result that the outcome is an one by one array, while array entering
it in two or more cells under each other or side by side gave as result an
one by three array.
As said I yet can't explain this. Maybe someone else who is reading this
too might.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Murtaza]

Thank Don!

Based on your suggested formula, I have made my day I have found a simple
array formula as per my requirements .

Following formula solved my problem.

{=SUM((B2:B4)*(LOOKUP(A2:A4,PriceList!$A$2:$A$34,PriceList!$B$2:$B$34)))}

Today i m feeling little bit expert in Excel.........you know sense of
achievment, but still please provide your comments or if i am missing
somthing.

Happy New Year
Murtaza