Problem with XLOOKUP on array

[sharshra]

I have to combine 2 tables using XLOOKUP. When I write formula in one cell & copy across other cells, I´m getting the correct result. When I use dynamic array, I get only the first column. 2nd column is not coming. I see that the output array is not spilled to 2nd column. Please help to resolve.

Also, another minor issue is that when there is no value in reference table, I´m getting 0. If there is no value, output should be blank, not 0.

Source table:

excel problems.xlsx
	B	C
3	name	entry
4	dfds	1
5	gfh	no
6	uir	7
7	qewr	8.4
8	xcvz	no
9	cmvn	2.55
10	dhgdh	no
11	teywu	3
12	sdf	4
13	gd	no
14	hkfi	no
Sheet6

Reference table:

excel problems.xlsx
	E	F	G
3	name	comment1	comment2
4	dfds	c1-1
5	qewr
6	xcvz		c2-3
7	cmvn		c2-4
8	dhgdh	c1-5
9	teywu
10	sdf
11	gd		c2-8
Sheet6

Desired output:

excel problems.xlsx
	J	K	L	M
3	name	entry	comment1	comment2
4	dfds	1	c1-1
5	gfh	no
6	uir	7
7	qewr	8.4
8	xcvz	no		c2-3
9	cmvn	2.55		c2-4
10	dhgdh	no	c1-5
11	teywu	3
12	sdf	4
13	gd	no		c2-8
14	hkfi	no
Sheet6

Output thru XLOOKUP formula in single cell copied to other cells:

excel problems.xlsx
	O	P	Q	R
14	hkfi	no
Sheet6

Cell Formulas
Range	Formula
Q14	Q14	=XLOOKUP(Table25[@name],Table36[name],Table36[[comment1]:[comment2]],"")

excel problems.xlsx
	O	P	Q
13	gd	no	0
Sheet6

Cell Formulas
Range	Formula
Q13:R13	Q13	=XLOOKUP(Table25[@name],Table36[name],Table36[[comment1]:[comment2]],"")
Dynamic array formulas.

excel problems.xlsx
	O	P	Q	R
3	name	entry	comment1	comment2
4	dfds	1	c1-1	0
5	gfh	no
6	uir	7
7	qewr	8.4	0	0
8	xcvz	no	0	c2-3
9	cmvn	2.55	0	c2-4
10	dhgdh	no	c1-5	0
11	teywu	3	0	0
12	sdf	4	0	0
Sheet6

Cell Formulas
Range	Formula
Q4:R4,Q7:R12,Q5:Q6	Q4	=XLOOKUP(Table25[@name],Table36[name],Table36[[comment1]:[comment2]],"")
Dynamic array formulas.

Output thru XLOOKUP & dynamic array:

excel problems.xlsx
	T	U	V	W
3	name	entry	comment1	comment2
4	dfds	1	c1-1
5	gfh	no
6	uir	7
7	qewr	8.4	0
8	xcvz	no	0
9	cmvn	2.55	0
10	dhgdh	no	c1-5
11	teywu	3	0
12	sdf	4	0
13	gd	no	0
14	hkfi	no
Sheet6

Cell Formulas
Range	Formula
V4:V14	V4	=XLOOKUP(Table25[name],Table36[name],Table36[[comment1]:[comment2]],"")
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	O	P	Q	R
3	name	entry	comment1	comment2
4	dfds	1	c1-1
5	gfh	no
6	uir	7
7	qewr	8.4
8	xcvz	no		c2-3
9	cmvn	2.55		c2-4
10	dhgdh	no	c1-5
11	teywu	3
12	sdf	4
Sheet4

Cell Formulas
Range	Formula
Q4:R12	Q4	=DROP(REDUCE("",O4:O12,LAMBDA(x,y,VSTACK(x,XLOOKUP(y,E4:E11,F4:G11&"",{"",""})))),1)
Dynamic array formulas.

 

[sharshra]

Perfect!!! Thanks, @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.