IF statement with INDEX MATCH

[PunchDrunkDave]

Is it possible to use an IF statement and INDEX MATCH formula together? I've been trying to get this to work, but am having trouble with it. I have 2 different sheets, a 'Data Entry' sheet and a 'Category wins' sheet.

My goal with the formula is to (in F3 of 'Category wins') compare the home team's categories versus the opponent's categories and output a "W" if the category is won, a "L" if the category is lost, or a "T" if the category is tied. So for example, if Punch Drunk Blind Luck's FG% (F7 on Data Entry sheet) is > Team Hurlz's FG% (F10 on Data Entry sheet), then cell F1 on the Category wins sheet would display a "W". The TO category is the only category where it would be won by having fewer TOs than the opponent, so that would be a simple switch of the > sign to the < sign.

So if done correctly, the results for Week 1 for Punch Drunk Blind Luck would be W-W-W-L-L-W-W-L-L.

I figured the first step would be to create two helper columns (column D and E on 'Category wins' sheet), but I'm stuck on how to merge my IF statements with the INDEX MATCH. Please let me know if further clarification is needed. Thanks.

[Richard Buttrey]

Hi and welcome to to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use and no one wants to spend time rcreating what you already have.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload choose the Go Advanced button on the bottom right of your post, look underneath the post for 'Managae Attachments' and take it from there.

[PunchDrunkDave]

Ah, gotcha. Thanks for the tip, my apologies.

[Richard Buttrey]

See attached. You don't need the helper columns on the category wins sheet. I've deleted them.

The basic formula in D2 copied across and down is

Please Login or Register  to view this content.

[PunchDrunkDave]

Wow. Thank you very much Richard. I'm going to take a hard look at it to make sure I understand why your formula works.