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

5 Replies

Sort By
  • 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

     

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 07, 2024

    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.

    • BORMAC's avatar
      BORMAC
      Copper Contributor
      May 08, 2024

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

      • djclements's avatar
        djclements
        Bronze Contributor
        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...

Resources