formula for matching a cell to a range of cells

**adiefallguy** · 08-17-2010, 11:15 AM

Hi All,

im in need of some help before I pull all my hair out.

im using excel 2000 and am trying to do the following.

I would like to create a formula that checks to see if the value is cell H3 is also in cell range A9:F500, and then again for H4, H5, H6, H7 and H8 and then if they are all there have "winner" in cell H1.

I have tried several combination of if and or statements but I cant seem to get them to work on the range of cells only on indivual cells which isnt much good.

Thanks
Adie

**MrsGKL** · 08-17-2010, 11:22 AM

Hi,

There are I'm sure multiple ways to do this, but maybe you could try putting this formula in H1.

=IF(AND(COUNTIF($A$9:$F$500,H3)>=1,COUNTIF($A$9:$F$500,H4)>=1,COUNTIF($A$9:$F$500,H5)>=1,COUNTIF($A$9:$F$500,H6)>=1,COUNTIF($A$9:$F$500,H7)>=1),"winner","not winner")

**The Exceller** · 08-17-2010, 11:33 AM

I had a formula that needed revising. The correct addition is attached.

**adiefallguy** · 08-17-2010, 11:38 AM

thanks for the quick response there but im getting an invalid link error on the attachment

**The Exceller** · 08-17-2010, 11:43 AM

Try this attachemnt. Should work.

If not, here's what I've done:

Starting in cell J3 and going down to J8 type the following:

J3 =COUNTIF($A$9:$F$500,H3)
J4 =COUNTIF($A$9:$F$500,H4)
J5 =COUNTIF($A$9:$F$500,H5)
J6 =COUNTIF($A$9:$F$500,H6)
J7 =COUNTIF($A$9:$F$500,H7)
J8 =COUNTIF($A$9:$F$500,H8)

Then in cell H1 type:

=IF(SUM(J3:J8)>=6,"winner","loser")

**adiefallguy** · 08-17-2010, 11:46 AM

thanks so much mrsgkl, that works a treat

**The Exceller** · 08-17-2010, 11:48 AM

MrsGLK's formula works as well once you type it in cell H1. She just forgot to include cell H8 which is included below.

Glad we could help you out!

=IF(AND(COUNTIF($A$9:$F$500,H3)>=1,COUNTIF($A$9:$F$500,H4)>=1,COUNTIF($A$9:$F$500,H5)>=1,COUNTIF($A$9:$F$500,H6)>=1,COUNTIF($A$9:$F$500,H7)>=1,COUNTIF($A$9:$F$500,H8)>=1),"winner","not winner")