Find Max Value With 1 Criteria (No Array)

[fittdaddy]

See table below. I need to return the max value of Column B (week) if Column A (year) contains a value (such as 2011). This also needs to be accomplished without using an array. Any help would be greatly appreciated.

A B
Year Week
2011 1
2010 1
2012 1
2011 2
2010 2
2012 2
2011 3
2010 3
2012 3

 

[barry houdini]

If the weeks are always in ascending order try a formula like this

=LOOKUP(2,1/(A2:A100=2011),B2:B100)

 

[fittdaddy]

Sorry, the weeks probably wouldn't be in ascending order. Any other thoughts?

 

[T. Valko]

Sorry, the weeks probably wouldn't be in ascending order. Any other thoughts?

Try this...

Book1
	A	B	C	D	E
2	2011	1	_	2011	3
3	2010	1	_	_	_
4	2012	1	_	_	_
5	2011	2	_	_	_
6	2010	2	_	_	_
7	2012	2	_	_	_
8	2011	3	_	_	_
9	2010	3	_	_	_
10	2012	3	_	_	_
Sheet1

This formula entered in E2:

=MAX(INDEX((A2:A10=D2)*B2:B10,0))

 

[fittdaddy]

Thank you. That worked great.

 

[T. Valko]

Thank you. That worked great.

You're welcome. Thanks for the feedback!

 

[Manishankar]

Thank you so much.