IF(MATCH(A3:B3,Mix1!E7:F16),Mix1!T7,0), doesn't work, what will?

C

cbaugher

I posted this before and when I came back to look for it, I could't find it.
So I'm sorry if it's up here twice. I thought that the first fix I got worked
but then realized it didn't.
What I need is for the
MATCH statement (or any statement) to be TRUE only when the the values in A3
& B3 match exactly with two cells in a row of the array Mix1!E7:F16. Is
there a way to do this? Thankyou.
 
J

JulieD

Hi Chris

not sure if this is the best answer but
=IF(ISNA(INDEX(E7:E16,MATCH(A3&B3,E7:E16&F7:F16,0))),"Not found","found")
entered with control & shift & enter not just enter

this will return "found" if the values in A3 & B3 match with values in E & F
on the same row respectively.

Cheers
JulieD
 
P

Peo Sjoblom

If you mean that they much match in the same row you would need an array
formula

=IF(ISNUMBER(MATCH(1,(Mix1!$E$7:$E$16=A3)*(Mix1!$F$7:$F$16=B3),0)),Mix1!T7,"no match")

entered with ctrl + shift & enter


if you only need a match in both columns

=IF(AND(COUNTIF(Mix1!$E$7:$E$16,A3)>0,COUNTIF(Mix1!$F$7:$F$16,B3)>0),Mix1!T7,"No Match")


Regards,

Peo Sjoblom
 
J

Jason Morin

See my last response a little while ago:

=IF(SUMPRODUCT(--(COUNTIF(Mix1!E7:F16,Sheet4!A3:B3)>0))
=2,Mix1!T7,0)


HTH
Jason
Atlanta, GA
 
C

cbaugher

Just what I needed. Thanks

Peo Sjoblom said:
If you mean that they much match in the same row you would need an array
formula

=IF(ISNUMBER(MATCH(1,(Mix1!$E$7:$E$16=A3)*(Mix1!$F$7:$F$16=B3),0)),Mix1!T7,"no match")

entered with ctrl + shift & enter


if you only need a match in both columns

=IF(AND(COUNTIF(Mix1!$E$7:$E$16,A3)>0,COUNTIF(Mix1!$F$7:$F$16,B3)>0),Mix1!T7,"No Match")


Regards,

Peo Sjoblom
Click to expand...
 
Top