On Tue, 20 Dec 2005 10:40:03 -0800, "Sanford Lefkowitz"
wrote:

I have an array of numbers like
100 0.01
200 0.015
300 0.02
400 0.025
500 0.03

say the range name is tab1

and an array of numbers like (call it tab2)
150
250
350
I want to look up the numbers from tab2 in tab1 and multilply it by the 2nd
column from tab1 and add up the result.
So, I want 150*.01+250*.015+350*.02

I tried as an array formula =sum((vlookup(tab2,tab1,2)*tab2)
but this does not work.
What should I be doing?

TIA


Using your NAMEing, you could use this formula:

=SUMPRODUCT(tab2,LOOKUP(tab2,INDEX(tab1,,1),INDEX( tab1,,2)))

Note that INDEX(tab1,,1) and INDEX(tab1,,2) merely return the first and second
columns of tab1. So you could replace those terms with references to the
single columns (e.g. A1:A100 and B1:B100).

Also note that if any value in tab2 is less than the minimum value in tab1
col1, the formula will return #NA

HTH,

--ron