Looking for formula to inspect range of cells for a specific value

[rbinney]

Hello,

Hoping someone can point me in the right direction with an excel formula that will check for a specific value, and then provide a Yes/No test result.

Background:

I'm coaching a baseball team with 14 players on it. Only 9 can be on the field at any one time. The league rules stipulate that a player may not sit out more than two consecutive innings. I've built a simple spreadsheet that shows the position and the number of innings (see attached sample). I use this spreadsheet to help me plan the defensive lineup and what player will play when.

I'd like to build a formula that will check for a specific player's name in column A in the range of cells from E4 through J12 to see if that player has not been in the game for more than two consecutive innings, and then return some type of simple message (Yes/No; Error/no Error, etc.) in Column C.

I was wondering if a Vlookup formula might fit the bill, but wasn't sure how to create a lookup function that would be able to test to see if a particular player had or had not been in the game in one of previous two innings.

Would someone be able to guide me on this question?

Thanks!

Rick

[cffndncr]

Hi Rbinney,

See if this is what you are looking for. It counts the number of times the player name occurs in each 3-column range. If it's 0 for any 3 inning range (meaning they have not been in for more than 2 consecutive innings), it returns "ERROR", otherwise it leaves the cell blank.

[sktneer]

Try this........

In C4

Please Login or Register  to view this content.

and copy down.

[rbinney]

cffndncr & sktneer,

Thank you very much for the replies!

cffndncr- I think the formula that you suggested works! I've tested it a couple of times and I think it does exactly what I'd hoped for.

Many thanks for the quick replies.

Regards,

Rick

[sktneer]

Glad to help you. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.