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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

RobRooster

New Member
Joined
Feb 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

RobRooster

New Member
Joined
Feb 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

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
Top