J
JE McGimpsey
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=INDEX(C1:C5,MATCH("green Apple"&"California",A1:A5&B1:B5,FALSE))
=INDEX(C1:C5,MATCH("green Apple"&"California",A1:A5&B1:B5,FALSE))
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
SAM SEBAIHI
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?
=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?
D
Dave Peterson
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))
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))
G
Guest
=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
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
S
SAM SEBAIHI
Thank you so much!!
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------