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

#### NextWorldAngel

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

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

#### tony0682

##### Board Regular
You need to reword your question. It is very very confusing.

#### northwolves

##### Well-known Member
Code:
``=MAX((A1:A3)*(B1:B3="local")*(D1:D3=MAX((B1:B3="local")*(D1:D3))))``

Best Regards.

#### Oaktree

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

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

#### NextWorldAngel

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

#### Oaktree

##### MrExcel MVP

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
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
(northwolves' formula is also an array formula and requires CTRL+SHIFT+ENTER)

I'm doing that.

Replies
1
Views
237
Replies
20
Views
204
Replies
6
Views
77
Replies
1
Views
68
Replies
2
Views
39