Display FILTER results in the same order as the values in the criteria range

	A	C	E	F	H	I
1			2023	2024	Column
2					2024
3	Bike A	Shop1	500	60
4	Bike B	Shop4	100	150	Selected Data	Result
5	Bike C	Shop3	800	200	980	Bike G
6	Bike D	Shop2	90	150	60	Bike A
7	Bike E	Shop1	45	790	150	Bike B
8	Bike F	Shop1	600	40	150	Bike D
9	Bike G	Shop4	900	980

In Range I5:I8 I want to select the corresponding value from Column A based on the number in Range H5:H8 and the column in Cell H2.

With reference to the answer in this question I implemented this formula:

=IFERROR(SORTBY(FILTER(A3:A9,COUNTIF(H5:H8,XLOOKUP(H2,E1:F1,E3:F9,"")),""),H5:H8),"")

With this formula the result looks like this:

Bike D
Bike G
Bike B
Bike A

This is almost correct since it includes all the values that I am searching for.
However, I would like to have them in the same order as the values in Range H5:H8.

How do I need to modify the formula to make it work?

Solution

Here is one way of achieving the desired outcome:

=LET(
     α, H5:H8,
     δ, XLOOKUP(H2,E1:F1,E3:F9,""),
     φ, FILTER(HSTACK(A3:A9,δ),COUNTIF(α,δ)),
     SORTBY(TAKE(φ,,1), XMATCH(TAKE(φ,,-1),α)))

Or, Not sure which version of Excel you are using presently, so an alternative way.

=LET(
     α, H5:H8,
     δ, XLOOKUP(H2,E1:F1,E3:F9,""),
     Σ, FILTER(CHOOSE({1,2},A3:A9,δ),1-ISNA(XMATCH(δ,α))),
     SORTBY(INDEX(Σ,,1),XMATCH(INDEX(Σ,,2),α)))

I realized there is a flaw in the above formulas if the duplicate values in the selected data are not consecutive then above will give FALSE Positives:

=LET(
     α, H5:H8,
     δ, XLOOKUP(H2,E1:F1,E3:F9,""),
     φ, FILTER(HSTACK(A3:A9,δ),COUNTIFS(α,δ,α,"<>")),
     SORTBY(TAKE(φ,,1),XMATCH(SEQUENCE(ROWS(φ)),
     XMATCH(α,TAKE(φ,,-1))+MAP(α,LAMBDA(ε,COUNTIF(ε:H5,ε)*(ε<>"")))-1)))

One more addition to the above formula(Please refer the screenshot it takes care of few things, hope the screenshot helps to understand, cant help with other ways needs LAMBDA() helpers here):

=LET(
     _Data, A3:F9,
     _Col, XLOOKUP(H2,A1:F1,_Data,""),
     _SelectedData, H5:H8,
     _Fx, LAMBDA(r,s, MAP(r,LAMBDA(c,COUNTIF(s:c,c)/10+c))),
     TOCOL(IF(_Fx(_Col,TAKE(_Col,1))=TOROW(_Fx(_SelectedData,
     TAKE(_SelectedData,1))),TAKE(_Data,,1),NA()),3,1))

Test Case: