Xlookup on multiple columns Filtering

[scottishbigyin]

Good morning, afternoon, evening (depending on your time zone)
Got a rather interesting challenge for the Excel wizards.

Hopefully someone can stop me from ripping out what hair I have left.
In stages (user enters list of circuit references) this would then query a table that has been capturing data from sharepoint.

Book4
	A
1	Circuit Reference Input
2	Ref12345
3	Ref12346
4	Ref12347
5	Ref12348
6	Ref12349
7
Sheet1

Circuit references get transfered into sheet2 using formula =TRIM(UNIQUE(FILTER(Sheet1!A2:A9,(Sheet1!A2:A9<>""))))

Book4
	A
1	Filtered Cells
2	Ref12345
3	Ref12346
4	Ref12347
5	Ref12348
6	Ref12349
Sheet2

Cell Formulas
Range	Formula
A2:A6	A2	=TRIM(UNIQUE(FILTER(Sheet1!A2:A9,(Sheet1!A2:A9<>""))))
Dynamic array formulas.

An Example of the Table where the Xlookup would use be, the Circuit 1 and Circuit 2 cannot be combined into 1

Book5
	A	B	C	D	E	F
2	Site ID	Customer	Board Name/Partner Authority	Circuit 1	Circuit 2	Column1
3	Site A	Circuit_000008	Comms	THIRD PARTY SUPPLIER	Ref12345	Ref12349
4	Site B	Circuit_000009	Comms	THIRD PARTY SUPPLIER	Ref12346
5	Site C	Circuit_000010	Comms	THIRD PARTY SUPPLIER	Ref12347
6	Site D	Circuit_000011	Comms	THIRD PARTY SUPPLIER	Ref12348
7	Site E	Circuit_000012	Comms	THIRD PARTY SUPPLIER	Ref12355	Ref12350
8	Site F	Circuit_000013	Comms	THIRD PARTY SUPPLIER	Ref12351
Sheet3

=XLOOKUP(Input!$A$4,Input!$A$2:$C$6,$A$2,0)

So my thinking was using formula on new sheet such as "=XLOOKUP(Sheet2!$A$2,Sheet3!$E$3:$E$8,$A$2,"",0)&XLOOKUP(Sheet2!$A$2,Sheet3!$F$3:$E$8,$A$2,"",0) and then duplicate down the rows in new sheet, but this will give blank if not found, so was wondering if way to ignore the rows that doesn't match either circuit 1 or circuit 2 column, without using any form of VBA just done via formula only.


If needing any clarification on this will try where I can.

 

[scottishbigyin]

Just to add the table that holds all the circuit information is over 6000 rows long, if going to impact the formula, since was thinking along possible lines of IF MATCH INDEX statement, or creating a unique number > 0 if circuit is found

 

[scottishbigyin]

Any assistance would be great