"Lookup problem"

[Jan Kronsell]

I have three columns. A and B is text and C is numbers.

I use

=SUMPRODUCT((A1:A70="Txt1")*(B1:B70="Txt2")*(C1:C70))

to find the value of C, where both a criteria in A and B is OK. This works
perfectly all right.

Now the challenge.

The combination of Txt1 and Txt2 can be found in more rows than one.
Therefore the SUMPRODUCT adds them up, as supposed to. What im interested in
though is findes not the sum, but the smallest C value, that meets the
criteria in A and B columns.I have tried

=SUMPRODUCT((A1:A70="Txt1")*(B1:B70="Txt2")*MIN(C1:C70)) but that didnt
work.

Example

A B 7
B B 2
C B 4
A B 5

In this situation i wanty the formula to return 5 for the criterias A and B.

Any suggestions?

Regards
Jan

 

[Bob Phillips]

=MIN(IF((A1:A70="Txt1")*(B1:B70="Txt2"),C1:C70))

this is an array formula so commit with Ctrl-Shift-Enter


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jan Kronsell]

Thnaks

Jan

=MIN(IF((A1:A70="Txt1")*(B1:B70="Txt2"),C1:C70))

this is an array formula so commit with Ctrl-Shift-Enter