vlookup question

[KJ]

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

 

[Eduardo]

Hi,
Could you please post your formula, thanks

 

[KJ]

I gave range names to each of the data sets Q1, Q2, ....

On the summary tab, my formula is: vlookup(unit # cell, Q1, 4- column with
the first subtotal I need,false)+vlookup(unit # cell, Q2, 4,
false)+vlookup(unit # cell, Q3, 4, false)+vlookup(unit # cell, Q4, 4, false)

Obviously if this unit # was added in quarter 3, it will return an error.
Hope this helps.

 

[Eduardo]

Hi,
try

=SUMPRODUCT(--(A1=Sheet2!A1:A9),Sheet2!B1:B9)+SUMPRODUCT(--(A1=Sheet3!A1:A9),Sheet3!B1:B9)

Change the above formula to fit your needs you will have to add to more + in
order to get Q3 and Q4

To help you, A1 isn the cell where you have the names to lookup
Sheet2!A1:A9 will represent the Q1, where the names are in column A
Sheet2!B1:B9 is the column where you have the amounts to be summarized

2nd part of the formula the same steps now but Sheet3 represents Q2

 

[Eduardo]

oops I press post too soon

the difference with sumproduct is that will look for the value and will add
if it find it, if not it will not give you the #N/A error

 

[KJ]

Thanks! I'll give it a shot.

Hi,
try

=SUMPRODUCT(--(A1=Sheet2!A1:A9),Sheet2!B1:B9)+SUMPRODUCT(--(A1=Sheet3!A1:A9),Sheet3!B1:B9)

Change the above formula to fit your needs you will have to add to more + in
order to get Q3 and Q4

To help you, A1 isn the cell where you have the names to lookup
Sheet2!A1:A9 will represent the Q1, where the names are in column A
Sheet2!B1:B9 is the column where you have the amounts to be summarized

2nd part of the formula the same steps now but Sheet3 represents Q2

 

[M Thompson]

Hi KJ
I know you said you're wanting to use vlookup, but have you considered using
Data>Consolidate? It works pretty well for pulling it all together if your
worksheets are set up the same-even if you have vehicles on some pages but
not on others.

Have a great weekend...