Scratching my head

[jarhead58]

Scenario: [TABLE="width: 238"]
<tbody>[TR]
[TD="align: right"]B2:B5
39[/TD]
[TD="align: right"]
48[/TD]
[TD="align: right"]
53[/TD]
[TD="align: right"]
67[/TD]
[TD="align: right"]
68
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

L53:P53
12 14 20 39 40

Why wouldn't this formula give back a "TRUE" value? I did do the Ctrl-Shift-Enter for the formula.

=INDEX(B2:F5,MATCH(B2:B5,L53:P53,0),"TRUE","FALSE")

TIA

 

[jarhead58]

Something happened to the text

B2:B5

39 48 53 67 68

 

[NickAtNight]

I have no clue what you are trying to do here.

match ( lookup value , lookup array , match type)

Your lookup array is. 12 14 20 39 40

and your match type says to use the first value found.

but instead of a lookup value, you have a range of values... only one of which is going to match the lookup array (39)

 

[NickAtNight]

And the INDEX formula does not match the entries you have.

 

[jarhead58]

And the INDEX formula does not match the entries you have.

My bad, should have been B2:F5,L53:P53, sorry.

 

[jarhead58]

Try this:

=SUMPRODUCT(--(B2:B6=L53:P53))>0

I had a typo in my formula when I was copying from one of my notes. It should have been ...B2:F2, L53:P53. Just trying to see if any numbers I enter from my source B2:F2 are matches to my list L53:P53

 

[Scott Huish]

Try this:

=SUMPRODUCT(COUNTIF(B2:F2,L53:P53))>0

 

[jarhead58]

Try this:

=SUMPRODUCT(COUNTIF(B2:F2,L53:P53))>0

Yes, that was it!! Thank you very much!!

 

[jarhead58]

Ok, I thought I had the formula the way I wanted it but I think my criteria is wrong. I am using Scotts formula; =SUMPRODUCT(COUNTIF(B2:F2,L53:P53))>0. I need the cell to be TRUE if 3 or more cells in B2:F2 match any of the cells in L53:P53 OR if 2 cells AND G2 = Q53, do you see what I'm looking for?

TIA

 

[jarhead58]

Ok, I thought I had the formula the way I wanted it but I think my criteria is wrong. I am using Scotts formula; =SUMPRODUCT(COUNTIF(B2:F2,L53:P53))>0. I need the cell to be TRUE if 3 or more cells in B2:F2 match any of the cells in L53:P53 OR if 2 cells AND G2 = Q53, do you see what I'm looking for?

TIA

So, nobody can help me out?