Match Function

[mark1]

Is there a way to do more than one match function? I have
three columns: A, B and C. I want to do a match function
that returns the relative position of the item where A and
B are equal and C is greater than 0.

 

[Frank Kabel]

Hi
maybe the following array formula (entered with CTRL+SHIFT+ENTER)
=MATCH(1,(A1:A100=B1:B100)*(C1:C100>0),0)

 

[mark1]

Hey Frank!
Wanted to know two things: In your formula below, what
does the 1 do in the formula? Why does it come first
thing after the open parentheses? If I'm reading the help
file correctly, 1 would be what you are trying to match.
Unless you are saying that (A1:A100=B1:B100)*(C1:C100>0)
is equal to 1 because it is TRUE. OK, I got that. Next
question - Can you use the multiplication sign in any
function if you want to use multiple criteria?

 

[mark1]

Hey Frank!
Wanted to know two things: In your formula below, what
does the 1 do in the formula? Why does it come first
thing after the open parentheses? If I'm reading the help
file correctly, 1 would be what you are trying to match.
Unless you are saying that (A1:A100=B1:B100)*(C1:C100>0)
is equal to 1 because it is TRUE. OK, I got that. Next
question - Can you use the multiplication sign in any
function if you want to use multiple criteria?

 

[Frank Kabel]

Hi
you got it correctly. and yes you could use the multiplication of
criteria to emulate the AND function

 

[mark1]

I have heard that the + sign mimics an OR statement. Is
that true? (We may have already discussed this).

 

[Frank Kabel]

Hi
yes this is correct