Lookup function with mulitple conditions

Tiffany · 08-15-2006, 09:25 PM

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 · 08-15-2006, 10:00 PM

One way:

J1 = AHS
K1 = MINI
L1 = PIONEER

=SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)

Biff

"Tiffany" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Max · 08-15-2006, 10:20 PM

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

Max · 08-15-2006, 10:30 PM

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
---

Tiffany · 08-15-2006, 11:00 PM

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
>

Max · 08-16-2006, 02:50 AM

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!

[email protected] · 08-20-2006, 11:10 AM

Biff wrote:
> One way:
>
> J1 = AHS
> K1 = MINI
> L1 = PIONEER
>
> =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)
>
> Biff
>
> "Tiffany" <[email protected]> wrote in message
> news:[email protected]...
> > 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

Biff · 08-21-2006, 01:45 AM

> 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

<[email protected]> wrote in message
news:[email protected]...
>
> Biff wrote:
>> One way:
>>
>> J1 = AHS
>> K1 = MINI
>> L1 = PIONEER
>>
>> =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)
>>
>> Biff
>>
>> "Tiffany" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>