IF Formula ?

[EVR_99]

Currently cell C9=”Play” (But it can also = “Kicks off”, “Field Goal”, “Punt”)
Cell D9=”Pass” (selected by a DV list used only if C9=”Play” did this just to differentiate between a Pass or Run play)
Cell E9=134(result of a Marco button, random #)
The formula below is for cell F9
=IF($C9="Play",IF($D9="Rush",VLOOKUP($E9,Run,2,FALSE),IF(D9="Pass",VLOOKUP($E9,Pass,2,FALSE),IF(C9="Kicks off",VLOOKUP($E9,Kickoff,2,FALSE),IF(C9="Punt",VLOOKUP($E9,Punt,2,FALSE),IF(C9="Field Goal",VLOOKUP($E9,FG,2,FALSE)))))))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Working fine until C9=Something other than “Play” then my F9 comes back False
If C9=ie “Kicks off”
My D9 cell is blank as desired
E9 is again generator by a Marco button, random #
So there is a value in E9 for lookup to find
But again my F9 comes back false
<o></o>
Doesn’t this formula look for a True statement until it can’t find one?
<o></o>
Thanks,
David

 

[ukmikeb]

Hi

You're missing two close brackets ")" after the second $D9 test, corrected formula -

=IF($C9="Play",IF($D9="Rush",VLOOKUP($E9,Run,2,FALSE),IF($D9="Pass",VLOOKUP($E9,Pass,2,FALSE)[COLOR=red][B]))[/B][/COLOR],IF($C9="Kicks off",VLOOKUP($E9,Kickoff,2,FALSE),IF($C9="Punt",VLOOKUP($E9,Punt,2,FALSE),IF($C9="Field Goal",VLOOKUP($E9,FG,2,FALSE)))))

hth

 

[EVR_99]

I now see the error of my ways

Thank you,
David

 

[ukmikeb]

David

The following -

=IF($C9="Play",VLOOKUP($E9,IF($D9="Rush",Run,Pass),2,FALSE),VLOOKUP($E9,CHOOSE(MATCH($D9,{"Field Goal","Kicks off","Punt"},0),FG,Kickoff,Punt),2,FALSE))

gives a different slant on the formula

hth