software

I need to compare differences in prices(Col D) between this year (sheet1) and
last year (sheet2) if they are the same Type of vehicle (Col A), Same colour
(Col B), and same type of transmission (Col C)

eg, Auto, Red Car will be $1,100 cheaper, because it is in last year the
same car costs $2100 compare to this year's $1,000
likewise, a blue AWD with Semi-auto would be $1,200 dearer compared to last
year.

in Sheet 1,[this year]
A B C D
1 Car Red Auto 1000
2 AWD Blue Semi 4500
3 Truck Yellow Auto 4500

In Sheet 2 [last year]
A B C D
1 Truck Green Auto 5000
2 Ute Yellow Man 3500
3 Car Red Auto 2100
4 AWD Blue Semi 3100

I tried using this formula but keep getting too many arguments.

=sheet1!$d1-INDEX(sheet2!$d$1:$d$4,MATCH(1,(--sheet1!$A1=sheet2!$a$1:$a$4),(--sheet1!$B1=sheet2!$b$1:$b$4),(--sheet1!$C1=sheet2!$c$1:$c$4)),0)

Any help will be much appreciated.In Sheet1 column E1, enter:
=D1-SUMPRODUCT((Sheet2!A$1:A$99=A1)*(Sheet2!B$1:B$99=B 1)*(Sheet2!C$1:C$99=C1),Sheet2!D$1$99)
then copy down

HTH
--
AP

quot;Kikkomanquot; gt; a écrit dans le message de
news: ...
gt;I need to compare differences in prices(Col D) between this year (sheet1)
gt;and
gt; last year (sheet2) if they are the same Type of vehicle (Col A), Same
gt; colour
gt; (Col B), and same type of transmission (Col C)
gt;
gt; eg, Auto, Red Car will be $1,100 cheaper, because it is in last year the
gt; same car costs $2100 compare to this year's $1,000
gt; likewise, a blue AWD with Semi-auto would be $1,200 dearer compared to
gt; last
gt; year.
gt;
gt; in Sheet 1,[this year]
gt; A B C D
gt; 1 Car Red Auto 1000
gt; 2 AWD Blue Semi 4500
gt; 3 Truck Yellow Auto 4500
gt;
gt; In Sheet 2 [last year]
gt; A B C D
gt; 1 Truck Green Auto 5000
gt; 2 Ute Yellow Man 3500
gt; 3 Car Red Auto 2100
gt; 4 AWD Blue Semi 3100
gt;
gt; I tried using this formula but keep getting too many arguments.
gt;
gt; =sheet1!$d1-INDEX(sheet2!$d$1:$d$4,MATCH(1,(--sheet1!$A1=sheet2!$a$1:$a$4),(--sheet1!$B1=sheet2!$b$1:$b$4),(--sheet1!$C1=sheet2!$c$1:$c$4)),0)
gt;
gt; Any help will be much appreciated.
gt;
The formula works really well, many thanks

quot;Ardus Petusquot; wrote:

gt; In Sheet1 column E1, enter:
gt; =D1-SUMPRODUCT((Sheet2!A$1:A$99=A1)*(Sheet2!B$1:B$99=B 1)*(Sheet2!C$1:C$99=C1),Sheet2!D$1$99)
gt; then copy down
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Kikkomanquot; gt; a écrit dans le message de
gt; news: ...
gt; gt;I need to compare differences in prices(Col D) between this year (sheet1)
gt; gt;and
gt; gt; last year (sheet2) if they are the same Type of vehicle (Col A), Same
gt; gt; colour
gt; gt; (Col B), and same type of transmission (Col C)
gt; gt;
gt; gt; eg, Auto, Red Car will be $1,100 cheaper, because it is in last year the
gt; gt; same car costs $2100 compare to this year's $1,000
gt; gt; likewise, a blue AWD with Semi-auto would be $1,200 dearer compared to
gt; gt; last
gt; gt; year.
gt; gt;
gt; gt; in Sheet 1,[this year]
gt; gt; A B C D
gt; gt; 1 Car Red Auto 1000
gt; gt; 2 AWD Blue Semi 4500
gt; gt; 3 Truck Yellow Auto 4500
gt; gt;
gt; gt; In Sheet 2 [last year]
gt; gt; A B C D
gt; gt; 1 Truck Green Auto 5000
gt; gt; 2 Ute Yellow Man 3500
gt; gt; 3 Car Red Auto 2100
gt; gt; 4 AWD Blue Semi 3100
gt; gt;
gt; gt; I tried using this formula but keep getting too many arguments.
gt; gt;
gt; gt; =sheet1!$d1-INDEX(sheet2!$d$1:$d$4,MATCH(1,(--sheet1!$A1=sheet2!$a$1:$a$4),(--sheet1!$B1=sheet2!$b$1:$b$4),(--sheet1!$C1=sheet2!$c$1:$c$4)),0)
gt; gt;
gt; gt; Any help will be much appreciated.
gt; gt;
gt;
gt;
gt;

=Sheet1!D1-INDEX(Sheet2!D14,MATCH(1,(Sheet1!A1=Sheet2!A1:A4 )*(Sheet1!B1=Sh
eet2!B1:B4)*(Sheet1!C1=Sheet2!C1:C4),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Kikkomanquot; gt; wrote in message
...
gt; I need to compare differences in prices(Col D) between this year (sheet1)
and
gt; last year (sheet2) if they are the same Type of vehicle (Col A), Same
colour
gt; (Col B), and same type of transmission (Col C)
gt;
gt; eg, Auto, Red Car will be $1,100 cheaper, because it is in last year the
gt; same car costs $2100 compare to this year's $1,000
gt; likewise, a blue AWD with Semi-auto would be $1,200 dearer compared to
last
gt; year.
gt;
gt; in Sheet 1,[this year]
gt; A B C D
gt; 1 Car Red Auto 1000
gt; 2 AWD Blue Semi 4500
gt; 3 Truck Yellow Auto 4500
gt;
gt; In Sheet 2 [last year]
gt; A B C D
gt; 1 Truck Green Auto 5000
gt; 2 Ute Yellow Man 3500
gt; 3 Car Red Auto 2100
gt; 4 AWD Blue Semi 3100
gt;
gt; I tried using this formula but keep getting too many arguments.
gt;
gt;
=sheet1!$d1-INDEX(sheet2!$d$1:$d$4,MATCH(1,(--sheet1!$A1=sheet2!$a$1:$a$4),(
--sheet1!$B1=sheet2!$b$1:$b$4),(--sheet1!$C1=sheet2!$c$1:$c$4)),0)
gt;
gt; Any help will be much appreciated.
gt;