Max or Large no to be found using multiple conditions

[mahen]

Hi All,


I need a formula as mentioned below. Many thanks for your help in advance

Based on column b it needs to filter the data in column A and provide me the Max value in column C, Example for Monday the value in column C should be 66.

A B C
12 Monday
73 Tuesday
45 Wednesday
27 Wednesday
66 Monday
51 Tuesday
58 Wednesday
34 Monday
14 Tuesday
81 Wednesday

 

[njimack]

Confirm with ctrl+shift+enter and copy down as far as necessary:
=MAX(IF($B$2:$B$11=B2,$A$2:$A$11))

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	12	Monday	66
2	73	Tuesday
3	45	Wednesday
4	27	Wednesday
5	66	Monday
6	51	Tuesday
7	58	Wednesday
8	34	Monday
9	14	Tuesday
10	81	Wednesday
Sheet1

 

[T. Valko]

Hi All,


I need a formula as mentioned below. Many thanks for your help in advance

Based on column b it needs to filter the data in column A and provide me the Max value in column C, Example for Monday the value in column C should be 66.

A B C
12 Monday
73 Tuesday
45 Wednesday
27 Wednesday
66 Monday
51 Tuesday
58 Wednesday
34 Monday
14 Tuesday
81 Wednesday

Try this array formula**:

=MAX(IF(B$2:B$11=B2,A$2:A$11))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

 

[NBVC]

=MAX(IF($B$1:$B$10,$B1,$C$1:$C$10))

adjust ranges to suit and confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

 

[mahen]

Hi All,

This forum is awesome, thank you all for your quick reply, the formula works the way i need it. This is an additional question to my first one, is there any possibility to integrate the same output with 2 more condition as explained below.

In column D when i filter for Jan, then in column C, i filter for Week 1 and column B when i filter for Monday, the output which i need to get in column E should be 56 which is highest number available for that condition.

A B C D E
12 Monday Week1 Jan 56
73 Tuesday Week1 Jan 73
45 Wednesday Week1 Jan
27 Wednesday Week1 Jan
66 Monday Week2 Feb
51 Tuesday Week1 Jan
58 Wednesday Week2 Feb
34 Monday Week2 Feb
14 Tuesday Week2 Feb
81 Wednesday Week2 Feb
67 Monday Week3 March
34 Tuesday Week2 Feb
89 Wednesday Week3 March
23 Wednesday Week3 March
12 Monday Week3 March
45 Tuesday Week3 March
78 Wednesday Week2 Feb
56 Monday Week1 Jan
34 Tuesday Week3 March
23 Wednesday Week1 Jan

Let me know if you need any further information, once again many thanks for your help.

Cheers,
Mahen

 

[njimack]

Excel Workbook
	A	B	C	D	E
1
2	12	Monday	Week1	Jan	56
3	73	Tuesday	Week1	Jan	73
4	45	Wednesday	Week1	Jan	45
5	27	Wednesday	Week1	Jan	45
6	66	Monday	Week2	Feb	66
7	51	Tuesday	Week1	Jan	73
8	58	Wednesday	Week2	Feb	81
9	34	Monday	Week2	Feb	66
10	14	Tuesday	Week2	Feb	34
11	81	Wednesday	Week2	Feb	81
12	67	Monday	Week3	March	67
13	34	Tuesday	Week2	Feb	34
14	89	Wednesday	Week3	March	89
15	23	Wednesday	Week3	March	89
16	12	Monday	Week3	March	67
17	45	Tuesday	Week3	March	45
18	78	Wednesday	Week2	Feb	81
19	56	Monday	Week1	Jan	56
20	34	Tuesday	Week3	March	45
21	23	Wednesday	Week1	Jan	45
Sheet1

 

[T. Valko]

Hi All,

This forum is awesome, thank you all for your quick reply, the formula works the way i need it. This is an additional question to my first one, is there any possibility to integrate the same output with 2 more condition as explained below.

In column D when i filter for Jan, then in column C, i filter for Week 1 and column B when i filter for Monday, the output which i need to get in column E should be 56 which is highest number available for that condition.

A B C D E
12 Monday Week1 Jan 56
73 Tuesday Week1 Jan 73
45 Wednesday Week1 Jan
27 Wednesday Week1 Jan
66 Monday Week2 Feb
51 Tuesday Week1 Jan
58 Wednesday Week2 Feb
34 Monday Week2 Feb
14 Tuesday Week2 Feb
81 Wednesday Week2 Feb
67 Monday Week3 March
34 Tuesday Week2 Feb
89 Wednesday Week3 March
23 Wednesday Week3 March
12 Monday Week3 March
45 Tuesday Week3 March
78 Wednesday Week2 Feb
56 Monday Week1 Jan
34 Tuesday Week3 March
23 Wednesday Week1 Jan

Let me know if you need any further information, once again many thanks for your help.

Cheers,
Mahen

Try this...

Still array entered**:

=MAX(IF(B$2:B$21=B2,IF(C$2:C$21=C2,IF(D$2:D$21=D2,A$2:A$21))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[mahen]

WOW!! you guys simply rock, such a quick reply and it feels like heaven Thank you so much for your help and i hope to come back to this forum frequently.

Once again thanks and have a good day ahead.

Cheers,
Mahen

 

[T. Valko]

WOW!! you guys simply rock, such a quick reply and it feels like heaven Thank you so much for your help and i hope to come back to this forum frequently.

Once again thanks and have a good day ahead.

Cheers,
Mahen

You're welcome. Thanks for the feedback!