Using an ISBLANK, INDEX and MATCH functions for a progressive AFL finals table

[M-Tommo]

Hi,

i am trying to create an AFL finals table now we know which 8 teams made the finals in order of who finished on top to who finished 8th, how it works is as the results come in and we know who won and who lost i enter an L for who lost and a W for who won next to the team in the yellow box.

I want to use the ISBLANK function so before I enter an L or a W the yellow boxes the blue boxes where the teams will automatically appear are all blank, once I put an L or W in the yellow boxes the blue boxes will update with the team that won or lost depending on the MATCH function.

I hope this makes sense.

I have attached a spreadsheet for you to see what I'm trying to do, I've put a formula in F8 with the result being FALSE as I put a scenario in cell E6 as L, the result should have been Port Power.

I hope someone can help

Cheers Mtommo

[JulianS96]

Is this what you were trying to do in cell F8?

Please Login or Register  to view this content.

[davsth]

Isblank is also looking at 2 cells which is problematic

perhaps
=IF(AND(ISBLANK(E6),ISBLANK(E7)),"",INDEX(D6:D7,MATCH(E3,E6:E7,0)))

or
=IF(AND(ISBLANK(E6),ISBLANK(E7)),"",INDEX(D6:D7,MATCH(E3,E6:E7,0)))

but whynot
=IF(E6="w",D6,IF(E6="L",D7,IF(E7="W",D7,IF(E7="l",D6,""))))

or put the score in e6 and e7
=if(and(e6<>"",e7<>""),if(e6>e7,d6,d7),"")

[Bob Phillips]

An alternative

=IFERROR(INDEX(D6:D7,MATCH("W",E6:E7,0)),"")

[M-Tommo]

Thanks guys for your quick response, they all worked fine, again thanks to JulianS96, Davsth and Bob Philips

cheers
M-Tommo