XLOOKUP lookup and return value that are not on the same row?

[karweng]

Is it possible to use XLOOKUP lookup and return value that are not on the same row?

Table 1 is the unstructured dataset that I'm trying use to look up from Table 2.

In Table 1 colA value ="1" is the Parent, and "2" is the Child and the number of rows are dynamic. And, the parent row containing the address.

example 1 Lookup Value Table2.A3 Lookup Array Table1.colB Return Array Table1.C3 & D3

example 2 Lookup Value Table2.A4 Lookup Array Table1.colB Return Array Table1.C5 & D5
example 3 Lookup Value Table2.A5 Lookup Array Table1.colB Return Array Table1.C5 & D5

 

[JamesCanale]

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	1		202 first street	melbourne			ABCD	2	202 first street	melbourne
3	2	ABCD	Joh	Doe			EFGG	4	55 Washington Ave	Seacaucus
4	1		55 Washington Ave	Seacaucus			CCFF	5	55 Washington Ave	Seacaucus
5	2	EFGG	Sara	Prince
6	2	CCFF	Dan	Prince
Sheet12

Cell Formulas
Range	Formula
H2:H4	H2	=MATCH(G2,$B$2:$B$6,0)
I2:I4	I2	=XLOOKUP(1,INDIRECT("$A$2:$A$"&H2+MIN(ROW($A$2:$A$6))-1),INDIRECT("$C$2:$C$"&H2+MIN(ROW($A$2:$A$6))-1),"",0,-1)
J2:J4	J2	=XLOOKUP(1,INDIRECT("$A$2:$A$"&H2+MIN(ROW($A$2:$A$6))-1),INDIRECT("$D$2:$D$"&H2+MIN(ROW($A$2:$A$6))-1),"",0,-1)

 

[karweng]

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	1		202 first street	melbourne			ABCD	2	202 first street	melbourne
3	2	ABCD	Joh	Doe			EFGG	4	55 Washington Ave	Seacaucus
4	1		55 Washington Ave	Seacaucus			CCFF	5	55 Washington Ave	Seacaucus
5	2	EFGG	Sara	Prince
6	2	CCFF	Dan	Prince
Sheet12

Cell Formulas
Range	Formula
H2:H4	H2	=MATCH(G2,$B$2:$B$6,0)
I2:I4	I2	=XLOOKUP(1,INDIRECT("$A$2:$A$"&H2+MIN(ROW($A$2:$A$6))-1),INDIRECT("$C$2:$C$"&H2+MIN(ROW($A$2:$A$6))-1),"",0,-1)
J2:J4	J2	=XLOOKUP(1,INDIRECT("$A$2:$A$"&H2+MIN(ROW($A$2:$A$6))-1),INDIRECT("$D$2:$D$"&H2+MIN(ROW($A$2:$A$6))-1),"",0,-1)

Thanks @JamesCanale this formula is awesome.

 

[Eric W]

Another way to write the formula without using the volatile INDIRECT:

Book1
	A	B	C	D	E	F	G	H	I
1
2	1		202 first street	melbourne			ABCD	202 first street	melbourne
3	2	ABCD	Joh	Doe			EFGG	55 Washington Ave	Seacaucus
4	1		55 Washington Ave	Seacaucus			CCFF	55 Washington Ave	Seacaucus
5	2	EFGG	Sara	Prince
6	2	CCFF	Dan	Prince
Sheet7

Cell Formulas
Range	Formula
H2:I4	H2	=XLOOKUP(1,$A$2:INDEX($A$2:$A$100,MATCH($G2,$B$2:$B$100,0)),C$2:INDEX(C$2:C$100,MATCH($G2,$B$2:$B$100,0)),"No match",0,-1)

 

[karweng]

Another way to write the formula without using the volatile INDIRECT:

Book1
	A	B	C	D	E	F	G	H	I
1
2	1		202 first street	melbourne			ABCD	202 first street	melbourne
3	2	ABCD	Joh	Doe			EFGG	55 Washington Ave	Seacaucus
4	1		55 Washington Ave	Seacaucus			CCFF	55 Washington Ave	Seacaucus
5	2	EFGG	Sara	Prince
6	2	CCFF	Dan	Prince
Sheet7

Cell Formulas
Range	Formula
H2:I4	H2	=XLOOKUP(1,$A$2:INDEX($A$2:$A$100,MATCH($G2,$B$2:$B$100,0)),C$2:INDEX(C$2:C$100,MATCH($G2,$B$2:$B$100,0)),"No match",0,-1)

Thanks Eric for providing the formula.