ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Lookup function with mulitple conditions (https://www.excelbanter.com/excel-worksheet-functions/105214-lookup-function-mulitple-conditions.html)

Tiffany
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

Biff
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

Max
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

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

Tiffany
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

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

[email protected]
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

Biff
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


All times are GMT +1. The time now is 07:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com