| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function with mulitple conditions
Hi,
I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 4 4 AHS ALL PHILIPS 13,480 17,491 5 5 AHS ALL PANA 24,121 17,792 6 6 AHS ALL PIONEER 13,912 17,456 7 0 AHS HTS ALL 47,947 58,236 8 0 AHS HTS SONY 4,821 6,094 9 0 AHS HTS LG 5,505 9,599 10 0 AHS HTS PIONEER 12,257 16,157 11 0 AHS HTS PHILIPS 3,058 4,505 12 0 AHS HTS PANA 9,016 7,462 13 0 AHS MICRO ALL 40,372 46,906 14 0 AHS MICRO LG 3,368 6,399 15 0 AHS MICRO PHILIPS 4,404 8,345 16 0 AHS MICRO PANA 14,149 9,818 17 0 AHS MICRO ENZER 1,306 5,940 18 0 AHS MICRO SONY 5,971 6,181 19 0 AHS MINI ALL 15,397 7,539 20 0 AHS MINI SONY 3,035 1,725 21 0 AHS MINI ENZER 2,022 1,402 22 0 AHS MINI PANA 920 467 23 0 AHS MINI JVC 1,506 1,668 24 0 AHS MINI PIONEER 1,625 496 25 0 CAM ALL ALL 93,560 87,068 26 0 CAM ALL SONY 30,611 32,035 27 0 CAM ALL PANA 24,689 18,065 28 0 CAM ALL CANON 16,456 15,465 29 0 CAM ALL JVC 15,359 16,587 30 0 CAM ALL SAMSUNG 2,186 2,982 31 0 CAM DVC ALL 80,020 55,815 32 0 CAM DVC CANON 16,456 12,803 33 0 CAM DVC PANA 23,139 15,824 34 0 CAM DVC SONY 21,821 14,421 35 0 CAM DVC JVC 13,626 9,802 36 0 CAM DVC SAMSUNG 2,186 2,468 37 0 CAM DVD ALL 9,830 20,958 38 0 CAM DVD SONY 7,270 15,514 39 0 CAM DVD PANA 1,379 1,960 40 0 CAM DVD CANON 0 2,662 41 0 CAM DVD HITACHI 1,181 822 42 0 CAM HDD ALL 0 4,035 43 0 CAM HDD JVC 0 3,763 44 0 CAM HDD SONY 0 272 45 0 CAM HDV ALL 102 1,400 46 0 CAM HDV SONY 102 1,400 47 0 CTV ALL ALL 204,332 256,280 48 0 CTV ALL SAMSUNG 17,551 31,500 49 0 CTV ALL SONY 19,924 27,473 50 0 CTV ALL PHILIPS 19,758 23,281 51 0 CTV ALL TOSHIBA 23,302 25,031 52 0 CTV ALL SHARP 26,857 30,535 53 0 CTV CRT ALL 136,889 123,255 54 0 CTV CRT JVC 15,259 17,459 55 0 CTV CRT PANA 22,104 19,415 56 0 CTV CRT SAMSUNG 7,230 8,608 57 0 CTV CRT SONY 17,442 14,533 58 0 CTV CRT PHILIPS 10,187 8,319 59 0 CTV RPTV ALL 7,608 3,048 60 0 CTV RPTV SAMSUNG 2,099 1,299 61 0 CTV RPTV TOSHIBA 3,203 917 62 0 CTV RPTV HITACHI 418 137 63 0 CTV RPTV PANA 890 251 64 0 CTV RPTV SONY 231 58 65 0 CTV FPD ALL 59,835 129,977 66 0 CTV FPD SAMSUNG 8,222 21,593 67 0 CTV FPD SONY 2,251 12,882 68 0 CTV FPD SHARP 12,674 19,104 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 Thank you |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
One way:
J1 = AHS K1 = MINI L1 = PIONEER =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71) Biff "Tiffany" wrote in message ... Hi, I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 4 4 AHS ALL PHILIPS 13,480 17,491 5 5 AHS ALL PANA 24,121 17,792 6 6 AHS ALL PIONEER 13,912 17,456 7 0 AHS HTS ALL 47,947 58,236 8 0 AHS HTS SONY 4,821 6,094 9 0 AHS HTS LG 5,505 9,599 10 0 AHS HTS PIONEER 12,257 16,157 11 0 AHS HTS PHILIPS 3,058 4,505 12 0 AHS HTS PANA 9,016 7,462 13 0 AHS MICRO ALL 40,372 46,906 14 0 AHS MICRO LG 3,368 6,399 15 0 AHS MICRO PHILIPS 4,404 8,345 16 0 AHS MICRO PANA 14,149 9,818 17 0 AHS MICRO ENZER 1,306 5,940 18 0 AHS MICRO SONY 5,971 6,181 19 0 AHS MINI ALL 15,397 7,539 20 0 AHS MINI SONY 3,035 1,725 21 0 AHS MINI ENZER 2,022 1,402 22 0 AHS MINI PANA 920 467 23 0 AHS MINI JVC 1,506 1,668 24 0 AHS MINI PIONEER 1,625 496 25 0 CAM ALL ALL 93,560 87,068 26 0 CAM ALL SONY 30,611 32,035 27 0 CAM ALL PANA 24,689 18,065 28 0 CAM ALL CANON 16,456 15,465 29 0 CAM ALL JVC 15,359 16,587 30 0 CAM ALL SAMSUNG 2,186 2,982 31 0 CAM DVC ALL 80,020 55,815 32 0 CAM DVC CANON 16,456 12,803 33 0 CAM DVC PANA 23,139 15,824 34 0 CAM DVC SONY 21,821 14,421 35 0 CAM DVC JVC 13,626 9,802 36 0 CAM DVC SAMSUNG 2,186 2,468 37 0 CAM DVD ALL 9,830 20,958 38 0 CAM DVD SONY 7,270 15,514 39 0 CAM DVD PANA 1,379 1,960 40 0 CAM DVD CANON 0 2,662 41 0 CAM DVD HITACHI 1,181 822 42 0 CAM HDD ALL 0 4,035 43 0 CAM HDD JVC 0 3,763 44 0 CAM HDD SONY 0 272 45 0 CAM HDV ALL 102 1,400 46 0 CAM HDV SONY 102 1,400 47 0 CTV ALL ALL 204,332 256,280 48 0 CTV ALL SAMSUNG 17,551 31,500 49 0 CTV ALL SONY 19,924 27,473 50 0 CTV ALL PHILIPS 19,758 23,281 51 0 CTV ALL TOSHIBA 23,302 25,031 52 0 CTV ALL SHARP 26,857 30,535 53 0 CTV CRT ALL 136,889 123,255 54 0 CTV CRT JVC 15,259 17,459 55 0 CTV CRT PANA 22,104 19,415 56 0 CTV CRT SAMSUNG 7,230 8,608 57 0 CTV CRT SONY 17,442 14,533 58 0 CTV CRT PHILIPS 10,187 8,319 59 0 CTV RPTV ALL 7,608 3,048 60 0 CTV RPTV SAMSUNG 2,099 1,299 61 0 CTV RPTV TOSHIBA 3,203 917 62 0 CTV RPTV HITACHI 418 137 63 0 CTV RPTV PANA 890 251 64 0 CTV RPTV SONY 231 58 65 0 CTV FPD ALL 59,835 129,977 66 0 CTV FPD SAMSUNG 8,222 21,593 67 0 CTV FPD SONY 2,251 12,882 68 0 CTV FPD SHARP 12,674 19,104 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 Thank you |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
One way ..
Assuming the table posted is within A1:G71, col A = Index, col C = Main Cat, col D = Sub Cat, col E = Brand Assuming I1:K1 will house the inputs for Main Cat, Sub Cat & Brand, Put in L1's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(COUNTA(I1:K1)<3,"",INDEX($A$2:$A$71,MATCH(1,($ C$2:$C$71=I1)*($D$2:$D$71=J1)*($E$2:$E$71=K1),0))) As-is, L1 can be copied down to return correspondingly for other sets of inputs in I2:K2, I3, K3 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiffany" wrote: I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 .... 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
Line
for other sets of inputs in I2:K2, I3, K3 etc should read: for other sets of inputs in I2:K2, I3:K3 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
Hi Max,
Thank you very much. Your formula works perfectly for me. Cheers! "Max" wrote: One way .. Assuming the table posted is within A1:G71, col A = Index, col C = Main Cat, col D = Sub Cat, col E = Brand Assuming I1:K1 will house the inputs for Main Cat, Sub Cat & Brand, Put in L1's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(COUNTA(I1:K1)<3,"",INDEX($A$2:$A$71,MATCH(1,($ C$2:$C$71=I1)*($D$2:$D$71=J1)*($E$2:$E$71=K1),0))) As-is, L1 can be copied down to return correspondingly for other sets of inputs in I2:K2, I3, K3 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiffany" wrote: I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 ... 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
You're welcome, Tiffany.
Thanks for calling back .. Believe Biff's suggestion also works equally well, is shorter* and doesn't require array-entering *even with the front error trap discounted It's always good to know of the various options available .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiffany" wrote: Hi Max, Thank you very much. Your formula works perfectly for me. Cheers! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
Biff wrote: One way: J1 = AHS K1 = MINI L1 = PIONEER =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71) Biff "Tiffany" wrote in message ... Hi, I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 4 4 AHS ALL PHILIPS 13,480 17,491 5 5 AHS ALL PANA 24,121 17,792 6 6 AHS ALL PIONEER 13,912 17,456 7 0 AHS HTS ALL 47,947 58,236 8 0 AHS HTS SONY 4,821 6,094 9 0 AHS HTS LG 5,505 9,599 10 0 AHS HTS PIONEER 12,257 16,157 11 0 AHS HTS PHILIPS 3,058 4,505 12 0 AHS HTS PANA 9,016 7,462 13 0 AHS MICRO ALL 40,372 46,906 14 0 AHS MICRO LG 3,368 6,399 15 0 AHS MICRO PHILIPS 4,404 8,345 16 0 AHS MICRO PANA 14,149 9,818 17 0 AHS MICRO ENZER 1,306 5,940 18 0 AHS MICRO SONY 5,971 6,181 19 0 AHS MINI ALL 15,397 7,539 20 0 AHS MINI SONY 3,035 1,725 21 0 AHS MINI ENZER 2,022 1,402 22 0 AHS MINI PANA 920 467 23 0 AHS MINI JVC 1,506 1,668 24 0 AHS MINI PIONEER 1,625 496 25 0 CAM ALL ALL 93,560 87,068 26 0 CAM ALL SONY 30,611 32,035 27 0 CAM ALL PANA 24,689 18,065 28 0 CAM ALL CANON 16,456 15,465 29 0 CAM ALL JVC 15,359 16,587 30 0 CAM ALL SAMSUNG 2,186 2,982 31 0 CAM DVC ALL 80,020 55,815 32 0 CAM DVC CANON 16,456 12,803 33 0 CAM DVC PANA 23,139 15,824 34 0 CAM DVC SONY 21,821 14,421 35 0 CAM DVC JVC 13,626 9,802 36 0 CAM DVC SAMSUNG 2,186 2,468 37 0 CAM DVD ALL 9,830 20,958 38 0 CAM DVD SONY 7,270 15,514 39 0 CAM DVD PANA 1,379 1,960 40 0 CAM DVD CANON 0 2,662 41 0 CAM DVD HITACHI 1,181 822 42 0 CAM HDD ALL 0 4,035 43 0 CAM HDD JVC 0 3,763 44 0 CAM HDD SONY 0 272 45 0 CAM HDV ALL 102 1,400 46 0 CAM HDV SONY 102 1,400 47 0 CTV ALL ALL 204,332 256,280 48 0 CTV ALL SAMSUNG 17,551 31,500 49 0 CTV ALL SONY 19,924 27,473 50 0 CTV ALL PHILIPS 19,758 23,281 51 0 CTV ALL TOSHIBA 23,302 25,031 52 0 CTV ALL SHARP 26,857 30,535 53 0 CTV CRT ALL 136,889 123,255 54 0 CTV CRT JVC 15,259 17,459 55 0 CTV CRT PANA 22,104 19,415 56 0 CTV CRT SAMSUNG 7,230 8,608 57 0 CTV CRT SONY 17,442 14,533 58 0 CTV CRT PHILIPS 10,187 8,319 59 0 CTV RPTV ALL 7,608 3,048 60 0 CTV RPTV SAMSUNG 2,099 1,299 61 0 CTV RPTV TOSHIBA 3,203 917 62 0 CTV RPTV HITACHI 418 137 63 0 CTV RPTV PANA 890 251 64 0 CTV RPTV SONY 231 58 65 0 CTV FPD ALL 59,835 129,977 66 0 CTV FPD SAMSUNG 8,222 21,593 67 0 CTV FPD SONY 2,251 12,882 68 0 CTV FPD SHARP 12,674 19,104 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 Thank you Biff, Please explain as to how your soln works. I read the sumproduct treats non numeric as zeroes. THanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function with mulitple conditions
Please explain as to how your soln works. I read the sumproduct
treats non numeric as zeroes. See this for a detailed explanation: http://xldynamic.com/source/xld.SUMPRODUCT.html Biff wrote in message ups.com... Biff wrote: One way: J1 = AHS K1 = MINI L1 = PIONEER =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71) Biff "Tiffany" wrote in message ... Hi, I need some help in the lookup function. Below is the excerpt of my database. How can I pick up the right row given 3 conditions to satisfy, ie Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI, Brand = PIONEER, what formula should I put in order to pick up Index = 24. Kindly advise. Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06 1 1 AHS ALL ALL 112,515 123,050 2 2 AHS ALL SONY 14,509 15,074 3 3 AHS ALL LG 13,056 17,837 4 4 AHS ALL PHILIPS 13,480 17,491 5 5 AHS ALL PANA 24,121 17,792 6 6 AHS ALL PIONEER 13,912 17,456 7 0 AHS HTS ALL 47,947 58,236 8 0 AHS HTS SONY 4,821 6,094 9 0 AHS HTS LG 5,505 9,599 10 0 AHS HTS PIONEER 12,257 16,157 11 0 AHS HTS PHILIPS 3,058 4,505 12 0 AHS HTS PANA 9,016 7,462 13 0 AHS MICRO ALL 40,372 46,906 14 0 AHS MICRO LG 3,368 6,399 15 0 AHS MICRO PHILIPS 4,404 8,345 16 0 AHS MICRO PANA 14,149 9,818 17 0 AHS MICRO ENZER 1,306 5,940 18 0 AHS MICRO SONY 5,971 6,181 19 0 AHS MINI ALL 15,397 7,539 20 0 AHS MINI SONY 3,035 1,725 21 0 AHS MINI ENZER 2,022 1,402 22 0 AHS MINI PANA 920 467 23 0 AHS MINI JVC 1,506 1,668 24 0 AHS MINI PIONEER 1,625 496 25 0 CAM ALL ALL 93,560 87,068 26 0 CAM ALL SONY 30,611 32,035 27 0 CAM ALL PANA 24,689 18,065 28 0 CAM ALL CANON 16,456 15,465 29 0 CAM ALL JVC 15,359 16,587 30 0 CAM ALL SAMSUNG 2,186 2,982 31 0 CAM DVC ALL 80,020 55,815 32 0 CAM DVC CANON 16,456 12,803 33 0 CAM DVC PANA 23,139 15,824 34 0 CAM DVC SONY 21,821 14,421 35 0 CAM DVC JVC 13,626 9,802 36 0 CAM DVC SAMSUNG 2,186 2,468 37 0 CAM DVD ALL 9,830 20,958 38 0 CAM DVD SONY 7,270 15,514 39 0 CAM DVD PANA 1,379 1,960 40 0 CAM DVD CANON 0 2,662 41 0 CAM DVD HITACHI 1,181 822 42 0 CAM HDD ALL 0 4,035 43 0 CAM HDD JVC 0 3,763 44 0 CAM HDD SONY 0 272 45 0 CAM HDV ALL 102 1,400 46 0 CAM HDV SONY 102 1,400 47 0 CTV ALL ALL 204,332 256,280 48 0 CTV ALL SAMSUNG 17,551 31,500 49 0 CTV ALL SONY 19,924 27,473 50 0 CTV ALL PHILIPS 19,758 23,281 51 0 CTV ALL TOSHIBA 23,302 25,031 52 0 CTV ALL SHARP 26,857 30,535 53 0 CTV CRT ALL 136,889 123,255 54 0 CTV CRT JVC 15,259 17,459 55 0 CTV CRT PANA 22,104 19,415 56 0 CTV CRT SAMSUNG 7,230 8,608 57 0 CTV CRT SONY 17,442 14,533 58 0 CTV CRT PHILIPS 10,187 8,319 59 0 CTV RPTV ALL 7,608 3,048 60 0 CTV RPTV SAMSUNG 2,099 1,299 61 0 CTV RPTV TOSHIBA 3,203 917 62 0 CTV RPTV HITACHI 418 137 63 0 CTV RPTV PANA 890 251 64 0 CTV RPTV SONY 231 58 65 0 CTV FPD ALL 59,835 129,977 66 0 CTV FPD SAMSUNG 8,222 21,593 67 0 CTV FPD SONY 2,251 12,882 68 0 CTV FPD SHARP 12,674 19,104 69 0 CTV FPD LG 6,379 11,865 70 0 CTV FPD PHILIPS 9,442 14,957 Thank you Biff, Please explain as to how your soln works. I read the sumproduct treats non numeric as zeroes. THanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| How to overcome LOOKUP function problems? | Excel Worksheet Functions | |||
| Lookup function problem (kg) | Excel Worksheet Functions | |||
| How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
Linear Mode
