Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Excel, I got a small database. I want to use DMAX() to find a value in it.

+--------+---------+---------+---------+
|        |  Cost   |         |         |
+--------+---------+---------+---------+
| Tree   | Market1 | Market2 | Market1 |
| Apples | 10      | 12      | 15      |
| Pear   | 15      | 13      | 16      |
| Cherry | 12      | 10      | 10      |
+--------+---------+---------+---------+

=DMAX(A1:D5,"Market1","Tree=Apples")

Problem is the Criteria fails, if I use 2 cells, one with "Tree" and one with "Apples" and I use criteria "G1:G2" it works just fine. Problem is I want to used DMAX in a second table, where I put the max prices for every Market and every Tree. This would mean I need TONS of cells for all the criteria. Instead, I'd like to do

=DMAX(A1:D5,C$13,$A$2&"="&$A3)

where C13 = Apples, A2 = Tree and A3 = Apples. This is so I can copy the DMAX formula to other places in my table.

Result would look like this:

+--------+---------+---------+
|        | Market1 | Market2 |
+--------+---------+---------+
| Apples |      15 |      12 |
| Pear   |      16 |      13 |
| Cherry |      12 |      10 |
+--------+---------+---------+
share|improve this question
up vote 1 down vote accepted

I've used the following weirdness to solve my problem:

=MAX(IF($A$2:$D$2=B$2,$B3:$D3))

Notice i had to use ctrl+shift+enter to enter the formula in the cell. This is called a array formula.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .