Re: Array Vlookups

How about using something like:

=SUMPRODUCT(VLOOKUP(A1,Sheet2!A:N,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))

If a cell is empty, then it will be treated as 0.

Adjust the range and columns to bring back.

I used A:N
and brought back the values in B:N (columns 2:13)



james.billy@xxxxxxxxx wrote:

All,

I want to lookup 12 columns and then add the results together, I know I
can do 12 separate vlookups and add the results but is there a more
efficient way of doing this? To complicate things I also need to
evaluate them incase one is zero or has been left blank. I was thinking
about a User Defined Function but have come unstuck in the past with
items not calculating when I want them to and as I want to give this to
quite a few people I want it as fool proof as possible.

For example, I have the months January - December and in each month I
have an item that I want to lookup and return the cost of that item to
give me an annual cost.

Hope this makes sense and thanks in advance,

James

--

Dave Peterson
.