=INDEX(C1:C5,MATCH(TRUE,ISNUMBER(SEARCH("Green
Apple"&"California",A1:A5&B1:B5)),0))

Array formula you have to commit with ctrl+shift+enter, not just enter

or

=INDEX(C1:C5,MATCH("Green Apple"&"California",INDEX(A1:A5&B1:B5,0),0))

none array formula just hit enter



"SAM SEBAIHI" wrote:


The following works perfectly when I have numbers as shown below.
=SUMPRODUCT(MAX((A1:A5="Apple")*(B1:B5="California ")*(C1:C5)))

A B C
Apple California $10
Orange California $5
Apple Florida $28
Orange Texas $5
Apple California $22

What if I have character instead of numbers. How would I do it?
as shown:
A B C
Apple California good
Orange California ok
madarine Florida Excellen
red Apple Texas expensive
green Apple California very expensive

if green apple and california were searched, then the result will be very
expensive. I don't want to sort these to use vlookup nor I want to use VB.
Is there a way?