Array Vlookups
 

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

Array Vlookups
 

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
|

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)



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

Array Vlookups
 

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

Array Vlookups
 

I think I'd add a 13th column to my data that did the summing. Then return that
value.

wrote:

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

--

Dave Peterson

Array Vlookups
 

Hi,

I have been experimenting, rather than using vlookups or matches I have
used sumifs, the main reason for this is that if the sumif doesn't
return an error it returns zero which means I then don't need to
evaluate the items that I want to lookup, for example before I would
have tested each of the 12 vlookups with an Iserror formula to ensure
that the formula didn't error. So my question now is that is this the
best approach? ie even though I am now not having to evaluate the
items, are sumifs dramatically slower than vlookups?

So my formula now is:


=SumIf(MyItems,B4,CostOfItems)/12+SumIf(MyItems,C4,CostOfItems)/12+SumIf(MyItems,D4,CostOfItems)/12+SumIf(MyItems,E4,CostOfItems)/12+SumIf(MyItems,F4,CostOfItems)/12+SumIf(MyItems,G4,CostOfItems)/12...


12 times, so until the criteria of the sumif equals M4.

Cheers,

James

Dave Peterson wrote:

I think I'd add a 13th column to my data that did the summing. Then return that
value.

wrote:

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

--

Dave Peterson

Array Vlookups
 

<are sumifs dramatically slower than vlookups?

If that is not evident, what does it matter?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I have been experimenting, rather than using vlookups or matches I have
| used sumifs, the main reason for this is that if the sumif doesn't
| return an error it returns zero which means I then don't need to
| evaluate the items that I want to lookup, for example before I would
| have tested each of the 12 vlookups with an Iserror formula to ensure
| that the formula didn't error. So my question now is that is this the
| best approach? ie even though I am now not having to evaluate the
| items, are sumifs dramatically slower than vlookups?
|
| So my formula now is:
|
|
|
=SumIf(MyItems,B4,CostOfItems)/12+SumIf(MyItems,C4,CostOfItems)/12+SumIf(MyItems,D4,CostOfItems)/12+SumIf(MyItems,E4,CostOfItems)/12+SumIf(MyItems,F4,CostOfItems)/12+SumIf(MyItems,G4,CostOfItems)/12...
|
|
| 12 times, so until the criteria of the sumif equals M4.
|
| Cheers,
|
| James
|
| Dave Peterson wrote:
|
| I think I'd add a 13th column to my data that did the summing. Then return that
| value.
|
| wrote:
|
| 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
|
| --
|
| Dave Peterson
|

Array Vlookups
 

Niek,

Firstly many thanks on your help with this. The reason I ask is that
Its difficult to simulate every scenario, this is just one sheet out of
32 and as this workbook will be going out to potentially 200 people on
200 different machines with varying amounts of data, I am just trying
to get the optimal performance possible (Any headaches I can avoid...).
And even though I can't see anything wrong, sometimes I read where
people just start saying never use this method etc. I just wanted to
make sure that this is not going to be the case... Plus if theres a
more elegant way of doing something then I am always interested in that
as well.

Cheers,

James

Niek Otten wrote:
<are sumifs dramatically slower than vlookups?

If that is not evident, what does it matter?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Hi,
|
| I have been experimenting, rather than using vlookups or matches I have
| used sumifs, the main reason for this is that if the sumif doesn't
| return an error it returns zero which means I then don't need to
| evaluate the items that I want to lookup, for example before I would
| have tested each of the 12 vlookups with an Iserror formula to ensure
| that the formula didn't error. So my question now is that is this the
| best approach? ie even though I am now not having to evaluate the
| items, are sumifs dramatically slower than vlookups?
|
| So my formula now is:
|
|
|
=SumIf(MyItems,B4,CostOfItems)/12+SumIf(MyItems,C4,CostOfItems)/12+SumIf(MyItems,D4,CostOfItems)/12+SumIf(MyItems,E4,CostOfItems)/12+SumIf(MyItems,F4,CostOfItems)/12+SumIf(MyItems,G4,CostOfItems)/12...
|
|
| 12 times, so until the criteria of the sumif equals M4.
|
| Cheers,
|
| James
|
| Dave Peterson wrote:
|
| I think I'd add a 13th column to my data that did the summing. Then return that
| value.
|
| wrote:
|
| 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
|
| --
|
| Dave Peterson
|

Array Vlookups
 

Understood!

Niek
wrote in message ups.com...
| Niek,
|
| Firstly many thanks on your help with this. The reason I ask is that
| Its difficult to simulate every scenario, this is just one sheet out of
| 32 and as this workbook will be going out to potentially 200 people on
| 200 different machines with varying amounts of data, I am just trying
| to get the optimal performance possible (Any headaches I can avoid...).
| And even though I can't see anything wrong, sometimes I read where
| people just start saying never use this method etc. I just wanted to
| make sure that this is not going to be the case... Plus if theres a
| more elegant way of doing something then I am always interested in that
| as well.
|
| Cheers,
|
| James
|
| Niek Otten wrote:
| <are sumifs dramatically slower than vlookups?
|
| If that is not evident, what does it matter?
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| wrote in message ups.com...
| | Hi,
| |
| | I have been experimenting, rather than using vlookups or matches I have
| | used sumifs, the main reason for this is that if the sumif doesn't
| | return an error it returns zero which means I then don't need to
| | evaluate the items that I want to lookup, for example before I would
| | have tested each of the 12 vlookups with an Iserror formula to ensure
| | that the formula didn't error. So my question now is that is this the
| | best approach? ie even though I am now not having to evaluate the
| | items, are sumifs dramatically slower than vlookups?
| |
| | So my formula now is:
| |
| |
| |
|
=SumIf(MyItems,B4,CostOfItems)/12+SumIf(MyItems,C4,CostOfItems)/12+SumIf(MyItems,D4,CostOfItems)/12+SumIf(MyItems,E4,CostOfItems)/12+SumIf(MyItems,F4,CostOfItems)/12+SumIf(MyItems,G4,CostOfItems)/12...
| |
| |
| | 12 times, so until the criteria of the sumif equals M4.
| |
| | Cheers,
| |
| | James
| |
| | Dave Peterson wrote:
| |
| | I think I'd add a 13th column to my data that did the summing. Then return that
| | value.
| |
| | wrote:
| |
| | 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
| |
| | --
| |
| | Dave Peterson
| |
|