Forum Discussion

BORMAC's avatar
BORMAC
Copper Contributor
May 07, 2024
Solved

Sorting with matching

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:
excel
Formulas and Functions
office 365
  • djclements's avatar
    djclements
    May 08, 2024

    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...

peiyezhu's avatar
peiyezhu
Bronze Contributor
May 07, 2024

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

 

 

 

Resources