# Returning the largest number based on multiple criteria

#### Mubz12

##### New Member
Hi All,

I am struggling to get something to work - I think it may be pretty simple but no matter how many different variations of MAX(IF) I try it does not seem to work.

In COLUMN U I have a range of large values and am grouping my data based on this. So for example greater than 10000 and less than 15000 would make one group.

I now want to return the highest value in COLUMN G based on the corresponding criteria specified above.

Any help would be appreciated!

Cheers.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### godsaaint

##### Active Member
Do Something like this(Array formula, confirm with Ctrl-Shift-Enter):

=LARGE(IF((U:U>10000)*(U:U<15000),U:U),1)

#### Mubz12

##### New Member
Hi,

Thanks for the swift response!

I tried the formula - and replaced the ending U:U with G:G as this the column I want the MAX of.

The formula is returning n/a# (confirmed with CTRL + SHIFT + ENTER)

Any further help would be greatly appreciated.

Thank you

#### godsaaint

##### Active Member
Hey mubz, if the formula is returning an error, is because is it not finding a number greater than 10000 and less than 15000 in the specified range. If you are sure there are, make sure the numbers are not stored as text.

By the way, instead of using G:G, is better to use the real range so excel doesn't calculate the entire column, for example G1:G1000

#### BarryL

##### Well-known Member

maybe the below entered with ctrl+shift+enter

=INDEX(G:G,LARGE(IF(U:U>10000,IF(U:U<15000,ROW(U:U))),1))

##### MrExcel MVP
Control+shift+enter, not just enter:

=MAX(IF(U2:U100>=10000,IF(U2:U100<=15000,G2:G100)))

#### Mubz12

##### New Member

Hi All,

Thanks you very much for the support. All formulas worked for what I needed to do - I ended using the formula from Aladin as my preferred method.

Thanks again all

Kind Regards

#### BarryL

##### Well-known Member
good stuff, glad you got it sorted.

##### MrExcel MVP
You are welcome.

Replies
2
Views
161
Replies
1
Views
434
Replies
4
Views
893
Replies
4
Views
248
Replies
1
Views
154

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,863
Messages
5,834,051
Members
430,259
Latest member
msthiagu

### 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.

### Which adblocker are you using?

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

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