# MAX / MIN question

#### sharky12345

##### Well-known Member
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.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Andrew Poulsom

##### MrExcel MVP
Adjust the range references to suit:

Excel 2010
ABCD
1
2AACRHT-01267
3Min1265ACRHT-01268
4Max1285ACRHT-01269
5BCRHT-01270
6ACRHT-01265
7ACRHT-01272
8ACRHT-01273
9BCRHT-01274
10ACRHT-01285
11ACRHT-01276
Sheet1
Cell Formulas
RangeFormula
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

##### Well-known Member
Brilliant - thanks Andrew!

#### sharky12345

##### Well-known Member
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

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

#### sharky12345

##### Well-known Member
Thanks - is there a way of dealing with that scenario?

#### Andrew Poulsom

##### MrExcel MVP
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

##### Well-known Member
Perfect, thank you!

Replies
5
Views
575
Replies
1
Views
154
Replies
14
Views
1K
Replies
6
Views
236
Replies
4
Views
250

1,191,232
Messages
5,985,425
Members
439,964
Latest member
nparrillo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back