Max Value from multiple columns and return item.

Micka77

New Member
Joined
Jul 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I am trying to find a way to return the max value from Value1 and Value2 along with the relevant item from column 1.
I can make this work with just max value as shown on cell A7.
A8 and A11 work in varying ways, but these are only looking at single columns, surely I’m missing something simple here?
CDA8EBE1-E70A-41B0-A710-F2AAAC67C1DD.jpeg
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
HIJ
8Water10030
9Gas200250
10Electricity300900
11Oil400500
12
13
14Electricity
Main
Cell Formulas
RangeFormula
I14I14=XLOOKUP(MAX(I8:J11),I8:I11,H8:H11,XLOOKUP(MAX(I8:J11),J8:J11,H8:H11,,0),0)
 

Micka77

New Member
Joined
Jul 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
HIJ
8Water10030
9Gas200250
10Electricity300900
11Oil400500
12
13
14Electricity
Main
Cell Formulas
RangeFormula
I14I14=XLOOKUP(MAX(I8:J11),I8:I11,H8:H11,XLOOKUP(MAX(I8:J11),J8:J11,H8:H11,,0),0)
Fluff thank you so much, that combined with a max formula in the next column gives me what I need. Brilliant.
Thanks for your kind welcome too.
Now I just hope I can change Max to a “Large” formula to return me the top 3 results which I look forward to solving.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,557
Members
417,220
Latest member
lam150498

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