MAX / MIN question

[sharky12345]

I'm trying to use a MAX formula but with criteria as below;

On Sheet2, a formula that will look at the column C2 to the last used row on Sheet1. If the cell matches the value of A2 on Sheet2, then look at the range A2 to last used column on Sheet1 and identify the highest number and display that number. The issue may be that the values in the range A2 onwards on Sheet1 are like this and are formatted as general;

CRHT-01267

If that can be achieved, I want to do the same but extract the minimum number.

 

[Andrew Poulsom]

Adjust the range references to suit:

Excel 2010
	A	B	C	D
1
2	A		A	CRHT-01267
3	Min	1265	A	CRHT-01268
4	Max	1285	A	CRHT-01269
5			B	CRHT-01270
6			A	CRHT-01265
7			A	CRHT-01272
8			A	CRHT-01273
9			B	CRHT-01274
10			A	CRHT-01285
11			A	CRHT-01276
Sheet1

Cell Formulas
Range	Formula
B3	{=MIN(IF(C2:C11=A2,RIGHT(D2:D11,LEN(D2:D11)-FIND("-",D2:D11))+0))}
B4	{=MAX(IF(C2:C11=A2,RIGHT(D2:D11,LEN(D2:D11)-FIND("-",D2:D11))+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sharky12345]

Brilliant - thanks Andrew!

 

[sharky12345]

Andrew,

I've used your solution for another sheet but any idea why it won't work and produces a '#VALUE!'? This is despite the fact that there are entries to find although in some cases only 1 if that makes a difference.....

=MAX(IF(REFERRALS!$G$2:$G$30000=B2,RIGHT(REFERRALS!$A$2:$A$30000,LEN(REFERRALS!$A$2:$A$30000)-FIND("-",REFERRALS!$A$2:$A$30000))+0))

 

[Andrew Poulsom]

You will get an error if one or more of the cells in REFERRALS!$A$2:$A$30000 doesn't contain a -.

 

[sharky12345]

Thanks - is there a way of dealing with that scenario?

 

[Andrew Poulsom]

Limit the referenced range or:

=MIN(IF(REFERRALS!G2:G30000=A2,IFERROR(RIGHT(REFERRALS!A2:A30000,LEN(REFERRALS!A2:A30000)-FIND("-",REFERRALS!A2:A30000))+0,"")))

=MAX(IF(REFERRALS!G2:G30000=A2,IFERROR(RIGHT(REFERRALS!A2:A30000,LEN(REFERRALS!A2:A30000)-FIND("-",REFERRALS!A2:A30000))+0,"")))

 

[sharky12345]

Perfect, thank you!