Hi James,

It may look inefficient, but do you really notice any delay?

If so, an alternative is to use MATCH() to locate the row once, and then use INDEX(), which is lightning fast, to retrieve the
items from that row.

The problem of a UDF not recalculating is usually that not all inputs are included in the argument list. The only way for Excel to
know when to recalculate is to include all precedents in the argument list.
But even then, you'll probably notice that it is very hard to get even near the performance of built-in functions, how inefficient
they may look. But that differs from case to case, of course.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| 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
|