Finding a cell that matches two max criteria.

RobRooster

New Member
Joined
Feb 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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:
Capture.PNG
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
Solution
Thank you for the answers. Im new here and did not know about the ad-in, will defenitely try it out.
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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?

Disable AdBlock

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
Back
Top