# Excel database criteria, use string as input instead of multiple cells

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 |
+--------+---------+---------+
``````
-

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.

-