# ELO ranking lookup problem

1. ## ELO ranking lookup problem

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  Register To Reply

2. ## Re: ELO ranking lookup problem

In cell E39...

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: ELO ranking lookup problem

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  Register To Reply

4. ## Re: ELO ranking lookup problem

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...  Register To Reply

5. ## Re: ELO ranking lookup problem

Hi jhren,

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

6. ## Re: ELO ranking lookup problem Originally Posted by rugbytrader 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))  Register To Reply

7. ## Re: ELO ranking lookup problem

jhren, you are an absolute legend!

thank you so, so much  Register To Reply

8. ## Re: ELO ranking lookup problem  Register To Reply