ELO ranking lookup problem

[rugbytrader]

Hi,

I'm having an issue creating a lookup on my ELO ratings table
I'm trying to put a formula in E39 that will look up the value in C39 and compare it to the values in C29:D38, find the equivalent value then pull the value in from the corresponding cell at Z29:AA38
Additionally, as the sheet gets bigger there will be multiple repeats of values in C29:D38, at the moment C39 is looking for the value SUN, I need to make sure it just pulls the value of the last SUN from the range, starting from the bottom

thanks for any help

[jhren]

In cell E39...

Please Login or Register  to view this content.

[rugbytrader]

2009 season.xlsx
Hi Jhren, that nearly works, I pull it down and it works for the first 2 rows, then some errors start creeping in.

I can't see what's going wrong

[jhren]

The errors start with E41 because the formula searches C29:D40 for a match to "BIR". There is none. You'll have to add error handling to the formula, but I have no idea how you want the error handled...

The error in E42 is because the E41 error results in an associated error in Z41...

[rugbytrader]

Hi jhren,

but BIR appears in D37 so it should be pulling the associated value from Z37

[jhren]

Hi jhren,

but BIR appears in D37 so it should be pulling the associated value from Z37

You are correct... my eyes aren't what they used to be.

Anyway, found the problem. Need to make the MATCH an exact lookup (so horizontal index doesn't need to be sorted)....

=INDEX($Z$29:$AA38,SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW(C$29:D38))))-28,MATCH(C39,INDIRECT("C"&SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW($C$29:$D38))))&":D"&SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW($C$29:$D38))))),0))

[rugbytrader]

jhren, you are an absolute legend!

thank you so, so much

[jhren]

Your Welcome...!!!