I am trying to add classifications to my leagues based on year. Some leagues change classifications on a year-to-year basis.
I am trying to use XLOOKUP to look up the year and the league, and select the proper classification from a range.
The problem arises when a league changes classifications from year to year, and I end up with the same league name on multiple lines. See the example below.
I tried an array XLOOKUP:
=XLOOKUP(A4&B4,$F$3:$P$3&$E$4:$E$8,$F$4:$P$8))
... and I get #VALUE! for all the results.
Then I tried a nested XLOOKUP (the one on the XL2BB sheet below):
=XLOOKUP(A4,$F$3:$P$3,XLOOKUP(B4,$E$4:$E$8,$F$4:$P$8))
... and it works fine on leagues that never change classifications, but not on leagues that do. With leagues that do, it gives me correct results from the first line, but zeroes as the result on subsequent lines.
Obviously, I would have believed that even with multiple lines of the same league, it would look for the classification that matches the year properly, but apparently, as currently constructed, XLOOKUP doesn't work that way.
Please don't suggest I simply consolidate lines that have multiple leagues because there are hundreds of them, which will take me hours, and I'd rather not have to do that until we determine there is no solution for this.
Thanks!
I am trying to use XLOOKUP to look up the year and the league, and select the proper classification from a range.
The problem arises when a league changes classifications from year to year, and I end up with the same league name on multiple lines. See the example below.
I tried an array XLOOKUP:
=XLOOKUP(A4&B4,$F$3:$P$3&$E$4:$E$8,$F$4:$P$8))
... and I get #VALUE! for all the results.
Then I tried a nested XLOOKUP (the one on the XL2BB sheet below):
=XLOOKUP(A4,$F$3:$P$3,XLOOKUP(B4,$E$4:$E$8,$F$4:$P$8))
... and it works fine on leagues that never change classifications, but not on leagues that do. With leagues that do, it gives me correct results from the first line, but zeroes as the result on subsequent lines.
Obviously, I would have believed that even with multiple lines of the same league, it would look for the classification that matches the year properly, but apparently, as currently constructed, XLOOKUP doesn't work that way.
Please don't suggest I simply consolidate lines that have multiple leagues because there are hundreds of them, which will take me hours, and I'd rather not have to do that until we determine there is no solution for this.
Thanks!
| Year | League | Class | 1934 | 1935 | 1936 | 1937 | 1938 | 1939 | 1940 | 1941 | 1942 | 1943 | 1944 | ||
| 1934 | International | B | International | B | B | B | B | B | |||||||
| 1934 | South Atlantic | C | American | C | C | C | C | ||||||||
| 1935 | International | B | South Atlantic | C | C | C | |||||||||
| 1935 | South Atlantic | C | South Atlantic | B | B | B | B | ||||||||
| 1936 | International | B | South Atlantic | A | A | A | A | ||||||||
| 1936 | South Atlantic | C | |||||||||||||
| 1937 | International | B | |||||||||||||
| 1937 | South Atlantic | 0 | |||||||||||||
| 1938 | International | B | |||||||||||||
| 1938 | South Atlantic | 0 | |||||||||||||
| 1939 | South Atlantic | 0 | |||||||||||||
| 1940 | South Atlantic | 0 | |||||||||||||
| 1941 | American | C | |||||||||||||
| 1941 | South Atlantic | 0 | |||||||||||||
| 1942 | American | C | |||||||||||||
| 1942 | South Atlantic | 0 | |||||||||||||
| 1943 | American | C | |||||||||||||
| 1943 | South Atlantic | 0 | |||||||||||||
| 1944 | American | C | |||||||||||||
| 1944 | South Atlantic | 0 | |||||||||||||
