Sorting with matching

[BORMAC]

I need to match the 2 ranges to each other.
Find the cells on the right in the range on the left and put them in the right row.
This is best shown in the image:

[#2]

@BORMAC 

=LET(range1,A1:B10,

range2,D1:E5,

IFNA(DROP(REDUCE("",SEQUENCE(ROWS(range1)),

LAMBDA(u,v,

VSTACK(u,

HSTACK(

INDEX(range1,v,1),INDEX(range1,v,2),"",

IF(ISNUMBER(XMATCH(INDEX(range1,v,1)&INDEX(range1,v,2),INDEX(range2,,1)&INDEX(range2,,2))),HSTACK(INDEX(range1,v,1),INDEX(range1,v,2)),""))))),1),""))

This formula returns the expected result in my sample file.

[#3]

@BORMAC 

sql:

 select * from left_join limit 20;

select f01,f02,f03,b.f04 r,iif(b.f04 is null,null,f02) r2 from left_join a left join (select f04 from left_join) b on a.f01=b.f04

[#4]

@OliverScheurich 

Dziękuję za pomoc.
Od razu przepraszam, ale zapomniałem o 1 ważnym punkcie:
W moim problemie tylko kolumny B i E zawierają te same wartości, które trzeba porównać.
Kolumny A i D mają różne wartości i należy je posortować razem z sąsiednią komórką.

[#5 · Accepted solution]

@BORMAC As a variant, without using LAMBDA helper functions:

=LET(
    rowId, XMATCH(B1:B10, E1:E5),
    HSTACK(
        EXPAND(A1:B10,, 3, ""),
        IF(ISNUMBER(rowId), INDEX(D1:E5, rowId, {1,2}), "")
    )
)

Please see the attached workbook to convert the functions to your language settings...

[#6]

Great!
Thank you very much - you solved my problem!