One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

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


In article ,
"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?

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?

One more...

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

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?

--

Dave Peterson

=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?

Thank you so much!!

--------------------------------------------------------------------------------
"Teethless mama" wrote in message
...
=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?