Get the exact value in array

Question

I have a virtual array table as follows. It is generated from query, not from the cell:

Col1	Col2	Col3
A	E	F
Q	B	N
***	***	***
T	Y	I
R	H	J
W	X	M
***	***	***
G	L	K
A	O	P
***	***	***

I would like to concatenate the value of Col2 between each "***" and use the concatenated value to lookup for the final value. Here is my anticipated result.

Anticipated Result

Col1	Col2	Col3	Col4
A	E	F	APPLE
Q	B	N
***	***	***
T	Y	I	ORANGE
R	H	J
W	X	M
***	***	***
G	L	K	BANANA
A	O	P
***	***	***

Lookup Table of Col2 to Col4

ColX	ColY
EB	APPLE
YHX	ORANGE
LO	BANANA

What I tried

I tried to search for offset function. But I don't know how to handle the "cell_reference" in offset function, because the array table is virtually created by query. I also have difficulties in running the vlookup function through an virtually created array table.

Thanks for helping.

Answer 1

Here's a possible solution:

=ARRAYFORMULA(LET(
  data_,A2:C11,
  lkup,E2:F4,
  data,IFNA(VSTACK(,data_)),
  s,SEQUENCE(ROWS(data)),
  R,LAMBDA(a,SORT(a,s,)),
  int,SCAN(,INDEX(data,,2),LAMBDA(a,c,IF(c="***",,a&c))),
  cln,IF(QUERY({int;""},"offset 1",)<>"",,int),
  fll,R(SCAN(,R(cln),LAMBDA(a,c,IF(c="",a,c)))),
  col4_,TOCOL(IF(COUNTIFS(fll,fll,s,"<"&s),,VLOOKUP(fll,lkup,2,0)),2),
  HSTACK(data_,col4_)))

Replace A2:C11 with the formula generating the initial table and E2:F4 with the range containing the lookup table.