=Max Forumula, multiple criteria, pull another cell on row

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31
Okay I need a formula that finds the max number in column D, if column B="Local" and then whatever the max # is in row ___ pull the entry in in that same row but from column A for the answer.

Example:
(Col. A) (Col. B) (Col. D)
1 ------Local-------- $50
2------- OTR------- $125
3------ Local------- $100

Answer would be 3.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
Code:
=MAX((A1:A3)*(B1:B3="local")*(D1:D3=MAX((B1:B3="local")*(D1:D3))))

Best Regards.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Try:

=MATCH(MAX(IF(B1:B3="local",D1:D3)),IF(B1:B3="local",D1:D3),0)

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

(Note that your ranges must start in row 1)

northwolves: I think you meant

=MAX(ROW(A1:A3)*(B1:B3="local")*(D1:D3=MAX((B1:B3="local")*(D1:D3))))
 

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31

ADVERTISEMENT

Okay what if I can't start on row 1?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984

ADVERTISEMENT

Okay what if I can't start on row 1?

=MATCH(MAX(IF(B1:B3="local",D1:D3)),IF(B1:B3="local",D1:D3),0) tells you the position in your range where the match exists. If you start in row 5 and the formula returns 3, the formula is telling you the match is in the (5+(3-1)) =7th row.

Alternatively, northwolves' solution (incorporating ROW) has that adjustment embedded.
 

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31
Okay so wolf's should work? Because it's not. I'm getting a number back from the correct column but it's not the max number. Here's the formula as I adjusted it for my spreadsheet.

=MAX((A15:A300)*(B15:B300="local")*(D15:D300=MAX((B15:B300="local")*(D15:D300))))

Did I mess something up?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
(northwolves' formula is also an array formula and requires CTRL+SHIFT+ENTER)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,723
Members
410,630
Latest member
JFORTH97
Top