Problems with Find formula

[rmezzo77]

I'm trying to create a spreadsheet for a football pool and have need some help with a formula problem


On the first worksheet "Week 1 Spreads" I have a list of the games, spreads, & scores. In column "I" I have simple "IF" formulas to show who won against the spread.

[/IMG]


Here is where I'm having a problem. On another worksheet, I have a list of each persons bets. Below shows that Ryan bet 50pts on New England which is a winner as per the pic above. The formula i am using does not work because in cell I4 on the other worksheet, it has a formula in it not the actual text "New England" so it always comes up as a losing bet and a value of -50. If I go into I4 and write the text "New England" then it works and puts a value of 50 in cell E2.

[/IMG]


Is there a way around this? Please help

 

[schielrn]

Welcome to the Board!

Are there trailing or leading spaces? Sorry I cannot see the pictures here at work since they are blocked, but maybe in the If formula try putting trim around the cell reference to remove any trailing or leading spaces. There is something that doesn't match with the New England that is being brought back by the formula because Excel doesn't care if it is typed in or returned through the formula if they are the same.

 

[rmezzo77]

I am using a data verification list to choose the 32 teams so both places where it says New England should be exactly the same. I wanted to avoid spelling mistakes by using the pulldown lists.

This is the formula that is not working
=IF(ISNUMBER(FIND(C2,'Week 1 Spreads'!I4:I21)),D2,-D2)

cell C2 field is a data verification pulldown "New England"
cell D2 is a value of 50
On worksheet 'Week 1 Spreads' in cell I4, this is a formula that returns the text "New England"

C2 & I4 match so I must be missing something.



Note:
If I change the formula to =IF(ISNUMBER(FIND(C2,'Week 1 Spreads'!I4)),D2,-D2) then it works. Can I not use the find command across a range of cells?

 

[rmezzo77]

Went a different route and got it working.

I Used =IF(COUNTIF('Week 1 Spreads'!I4:I21,C2),D2,-D2)

 

[Aladin Akyurek]

...
Note:
If I change the formula to =IF(ISNUMBER(FIND(C2,'Week 1 Spreads'!I4)),D2,-D2) then it works. Can I not use the find command across a range of cells?

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
    SEARCH(C2,'Week 1 Spreads'!I4:I21))),D2,-D2)

Went a different route and got it working.

I Used =IF(COUNTIF('Week 1 Spreads'!I4:I21,C2),D2,-D2)

I think the LOOKUP version should be faster on the average.