XLOOKUP on Array with Same Entity on Multiple Lines

[chasfh]

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!

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

 

[Jeffrey Mahoney]

How about this?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	League	Class			1934	1935	1936	1937	1938	1939	1940	1941	1942	1943	1944
2	1934	International	B		International	B	B	B	B	B
3	1934	South Atlantic	C		American								C	C	C	C
4	1935	International	B		South Atlantic	C	C	C	0	0	0	0	0	0	0	0
5	1935	South Atlantic	C		South Atlantic	C	C	C	0	0	0	0	0	0	0	0
6	1936	International	B		South Atlantic	C	C	C	0	0	0	0	0	0	0	0
7	1936	South Atlantic	C
8	1937	International	B
9	1937	South Atlantic	0
10	1938	International	B
11	1938	South Atlantic	0
12	1939	South Atlantic	0
13	1940	South Atlantic	0
14	1941	American	C
15	1941	South Atlantic	0
16	1942	American	C
17	1942	South Atlantic	0
18	1943	American	C
19	1943	South Atlantic	0
20	1944	American	C
21	1944	South Atlantic	0
Sheet1

Cell Formulas
Range	Formula
F2:P6	F2	=IFERROR(XLOOKUP(F$1&$E2,$A$2:$A$21&$B$2:$B$21,$C$2:$C$21),"")

 

[Jeffrey Mahoney]

If you want to get rid of the ZERO returns, use this in Cell F2
=LET(Class,IFERROR(XLOOKUP(F$1&$E2,$A$2:$A$21&$B$2:$B$21,$C$2:$C$21),""),IF(Class=0,"",Class))

 

[chasfh]

If you want to get rid of the ZERO returns, use this in Cell F2
=LET(Class,IFERROR(XLOOKUP(F$1&$E2,$A$2:$A$21&$B$2:$B$21,$C$2:$C$21),""),IF(Class=0,"",Class))

I'm sorry, I may not have articulated the problem as properly as I might have.

The problem is not that zeroes are showing up instead of blanks; or that South Atlantic changes to B and then to A in the array. South Atlantic is supposed to change to B then A in the array, not remain C, or change to zeroes or blanks.

The problem is that when South Atlantic does change in the array to B in 1937, the result in cell C11 in the table (and C12 and C13) is a zero, when it should be B, as per the array. And then, when South Atlantic changes to A in 1941, the result in C17 (and C19 AND C21 AND C23) is a zero, when it should be A. I am not trying to change all the Bs and As in the array for South Atlantic to zeroes or blanks. They are supposed to stay that way in the array. What I am trying to change is the result in the table.

There are multiple rows for South Atlantic because that's the way the table came together from an outside source. But there is no duplication for South Atlantic at any time; South Atlantic is never B on one row and C on another row in the same year (column). There's mutual exclusivity. That's why I am trying to have the XLOOKUP read the array to deliver the different classifications for the different years, even if the league is listed on multiple rows. Consolidating all the teams listed on multiple rows—and there are a couple hundred of them—into single rows for each would take hours and hours of manual labor. That's what I'm trying to avoid.

Knowing that now, is there any way to accomplish what I am trying to?

 

[Jeffrey Mahoney]

Ok, I'll step back and ask this question. Are you trying to populate the Table in Columns E through P with the Data in Columns A through C?

 

[chasfh]

Ok, I'll step back and ask this question. Are you trying to populate the Table in Columns E through P with the Data in Columns A through C?

Other way around. I am trying to populate Column C with data from the array E3:P8, based on the lookup values in Column A (year) and Column B (league).

So, in row 9, based on the lookup values in cell A9 (1937) and cell B9 (South Atlantic), I want it to go the the array and pull the letter "B", which is where the two values intersect in the array in cell I5, and return that "B" to cell C9.

I tried both of the formulas in the original post and neither of them worked for me.

Thanks for checking up on that. Any ideas based on it?

 

[Jeffrey Mahoney]

Please try this

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	League	Class			1934	1935	1936	1937	1938	1939	1940	1941	1942	1943	1944
2	1934	International	B		International	B	B	B	B	B
3	1934	South Atlantic	C		American								C	C	C	C
4	1935	International	B		South Atlantic	C	C	C
5	1935	South Atlantic	C		South Atlantic				B	B	B	B
6	1936	International	B		South Atlantic								A	A	A	A
7	1936	South Atlantic	C
8	1937	International	B
9	1937	South Atlantic	B
10	1938	International	B
11	1938	South Atlantic	B
12	1939	South Atlantic	B
13	1940	South Atlantic	B
14	1941	American	C
15	1941	South Atlantic	A
16	1942	American	C
17	1942	South Atlantic	A
18	1943	American	C
19	1943	South Atlantic	A
20	1944	American	C
21	1944	South Atlantic	A
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=TEXTJOIN(,TRUE,FILTER(INDEX($F$2:$P$6,,MATCH($A2,$F$1:$P$1)),($E$2:$E$6=$B2)))

 

[Jeffrey Mahoney]

Any zeros in the right table will need to be removed. The formula works to remove blank cells. Search and replace entire cell contents for any zeros and replace with nothing.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	League	Class			1934	1935	1936	1937	1938	1939	1940	1941	1942	1943	1944
2	1934	International	B		International	B	B	B	B	B
3	1934	South Atlantic	C		American								C	C	C	C
4	1935	International	B		South Atlantic	C	C	C
5	1935	South Atlantic	C		South Atlantic				B	B	B	B
6	1936	International	B		South Atlantic								A	A	A	A
7	1936	South Atlantic	C
8	1937	International	B
9	1937	South Atlantic	B
10	1938	International	B
11	1938	South Atlantic	B
12	1939	South Atlantic	B
13	1940	South Atlantic	B
14	1941	American	C
15	1941	South Atlantic	A
16	1942	American	C
17	1942	South Atlantic	A
18	1943	American	C
19	1943	South Atlantic	A
20	1944	American	C
21	1944	South Atlantic	A
22
Data

Cell Formulas
Range	Formula
A2:C21	A2	=SORT(HSTACK(TOCOL(IF(F2:P6<>"",F1:P1,1/0),2),TOCOL(IF(F2:P6<>"",E2:E6,1/0),2),TOCOL(F2:P6,1)))
Dynamic array formulas.

 

[chasfh]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	League	Class			1934	1935	1936	1937	1938	1939	1940	1941	1942	1943	1944
2	1934	International	B		International	B	B	B	B	B
3	1934	South Atlantic	C		American								C	C	C	C
4	1935	International	B		South Atlantic	C	C	C
5	1935	South Atlantic	C		South Atlantic				B	B	B	B
6	1936	International	B		South Atlantic								A	A	A	A
7	1936	South Atlantic	C
8	1937	International	B
9	1937	South Atlantic	B
10	1938	International	B
11	1938	South Atlantic	B
12	1939	South Atlantic	B
13	1940	South Atlantic	B
14	1941	American	C
15	1941	South Atlantic	A
16	1942	American	C
17	1942	South Atlantic	A
18	1943	American	C
19	1943	South Atlantic	A
20	1944	American	C
21	1944	South Atlantic	A
22
Data

Cell Formulas
Range	Formula
A2:C21	A2	=SORT(HSTACK(TOCOL(IF(F2:P6<>"",F1:P1,1/0),2),TOCOL(IF(F2:P6<>"",E2:E6,1/0),2),TOCOL(F2:P6,1)))
Dynamic array formulas.

Not sure I'm doing it right, but I am getting a #SPILL! error.