Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!! Lookup multiple criteria
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!! Lookup multiple criteria
The formula works really well, many thanks
"Ardus Petus" wrote: 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:D$99) then copy down HTH -- AP "Kikkoman" a écrit dans le message de news: ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!! Lookup multiple criteria
=Sheet1!D1-INDEX(Sheet2!D1:D4,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) "Kikkoman" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get cell value with multiple criteria lookup | Excel Worksheet Functions | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions |