Populate Data ans Rearrange

[kumara_faith]

Hi,

I have the following table:

Ariba Table Formula.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Table 1
2	Data 1	Data 2	Data 3	Name	Vol 1	Vol 2	Vol 3	Vol 4	Vol 5	Vol 6	Vol 7	Vol 8	Vol 9	Vol 10
3	ABVC	ABVC	ABVC	Michale Nathan Williams	10	10	10	10	10	10	10	10	10	10
4	ABVC	ABVC	ABVC	Kerry Walley	10	10	10	10	10	10	10	10	10	10
5	ABVC	ABVC	ABVC	Martinez China Buthen	10	10	10	10	10	10	10	10	10	10
6
7
8				Table 2
9				Michale Nathan Williams	Texas
10				Amy Charles Baview	Washinton
11				Kerry Walley	Washinton
12				Mathew Valex	Texas
13				Martinez China Buthen	Washinton
Sheet1 (2)

I am trying to match the names from Table 2 with the names in Table 1 and populate the data from Table 1 including the Table headers. In addition, I need the State and Name to be in the first two columns. I am currently using this formula:

=LET(
    data_with_state, HSTACK(
        B3:D5,
        XLOOKUP(FILTER(E3:E5, ISNUMBER(MATCH(E3:E5, E9:E13, 0))), E9:E13, F9:F13),
        E3:O5
    ),
    final_result, VSTACK(
        HSTACK(B2:D2, "State", E2:O2),
        FILTER(data_with_state, ISNUMBER(MATCH(E3:E5, E9:E13, 0)))
    ),
    final_result
)

However the result are as follows:

Ariba Table Formula.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
30	Data 1	Data 2	Data 3	State	Name	Vol 1	Vol 2	Vol 3	Vol 4	Vol 5	Vol 6	Vol 7	Vol 8	Vol 9	Vol 10
31	ABVC	ABVC	ABVC	Texas	Michale Nathan Williams	10	10	10	10	10	10	10	10	10	10
32	ABVC	ABVC	ABVC	Washinton	Kerry Walley	10	10	10	10	10	10	10	10	10	10
33	ABVC	ABVC	ABVC	Washinton	Martinez China Buthen	10	10	10	10	10	10	10	10	10	10
Sheet1 (2)

Cell Formulas
Range	Formula
Q30:AE33	Q30	=LET( data_with_state, HSTACK( B3:D5, XLOOKUP(FILTER(E3:E5, ISNUMBER(MATCH(E3:E5, E9:E13, 0))), E9:E13, F9:F13), E3:O5 ), final_result, VSTACK( HSTACK(B2:D2, "State", E2:O2), FILTER(data_with_state, ISNUMBER(MATCH(E3:E5, E9:E13, 0))) ), final_result )
Dynamic array formulas.

The correct result should be as follows:

Ariba Table Formula.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
36	State	Name	Data 1	Data 2	Data 3	Vol 1	Vol 2	Vol 3	Vol 4	Vol 5	Vol 6	Vol 7	Vol 8	Vol 9	Vol 10
37	Texas	Michale Nathan Williams	ABVC	ABVC	ABVC	10	10	10	10	10	10	10	10	10	10
38	Washinton	Kerry Walley	ABVC	ABVC	ABVC	10	10	10	10	10	10	10	10	10	10
39	Washinton	Martinez China Buthen	ABVC	ABVC	ABVC	10	10	10	10	10	10	10	10	10	10
Sheet1 (2)

Appreciate any help.

 

[Fluff]

How about

=HSTACK(VSTACK("State",XLOOKUP(E3:E5,E9:E13,F9:F13)),CHOOSECOLS(B2:O5,4,1,2,3,SEQUENCE(,10,5)))

 

[kumara_faith]

Thank you Fluff. That worked. Appreciate the solution and have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.