Hi,

Niek - Thanks for this, I have started to see if Match/Index is faster.

Dave - Thanks, this is the route I initially took but the problem I
have is that I want to look up 12 items so I think the array is in the
wrong place, its almost as if I want to write the vlookup as
Vlookup({A1;B1;C1;D1;E1;F1;G1;...},SomeRange,2,fal se), which I am
starting to see isn't going to happen. - Which is a shame as this would
be exactly what I wanted, unless I am missing something?

Just to extend on the problem, I am creating a spreadsheet for salary
costs, the persons pay scale could change at some point during the year
so I have the person as one record and then 12 columns for their
payscales I then need to calculate the total salary for that employee
so I lookup each months pay scale and add them together, evaluating
each month to make sure it hasn't been removed (ie the person is
leaving mid way through the year).

Any futher comments would be very much appreciated,

Cheers,

James

Dave Peterson wrote:
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)



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