# Finding a cell that matches two max criteria.

#### RobRooster

##### New Member
Hello everyone,

I need help in figuring out how to write a formula that finds 1 cell that meets two max criteria. I have tried several formulas such as VLOOKUP, MAXIFS, and INDEX(MATCH). For my example I want a formula that chooses the option that has the highest effective % but also the highest times chosen. In this case it would be Option EE that has been chosen 4 times with a effectivity of 100%. Using the formula I have on the right side it gives me Option C which is the first option it encounters with 100%, although it is really not the most effective. I also need to find the Least effective which would be Option Z, but I figure that would be changing from MAX to MIN.

Any help will be greatly appreciated!!

Example Below:

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Can you post some sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### lrobbo314

##### Well-known Member
Possibly.

Book One.xlsx
BCDEFGH
2Times ChosenChosen %Times DoneEffective %Result
3A20.22100%D
4B30.3180%
5C50.4270%
6D50.53100%
Sheet3
Cell Formulas
RangeFormula
H3H3=INDEX(B3:B6,LARGE(--(ROW(C3:C6)*(F3:F6=MAX(F3:F6)))-ROW(C3)+1,1))

#### Fluff

##### MrExcel MVP, Moderator
Another possible option
Excel Formula:
``=INDEX(B3:B33,MATCH(1,(C3:C33=MAXIFS(C3:C33,F3:F33,MAX(F3:F33)))*(F3:F33=MAX(F3:F33)),0))``
This needs to confirmed with Ctrl Shift Enter, rather than just Enter.

#### RobRooster

##### New Member

Thank you for the answers. Im new here and did not know about the ad-in, will defenitely try it out.

#### RobRooster

##### New Member
Another possible option
Excel Formula:
``=INDEX(B3:B33,MATCH(1,(C3:C33=MAXIFS(C3:C33,F3:F33,MAX(F3:F33)))*(F3:F33=MAX(F3:F33)),0))``
This needs to confirmed with Ctrl Shift Enter, rather than just Enter.
Thank you! Ths formula did help for what I needed.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
1
Views
509
Replies
1
Views
143
Replies
7
Views
410
Replies
9
Views
120
Replies
20
Views
371

1,127,523
Messages
5,625,308
Members
416,090
Latest member
Amneziak

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