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

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

    • BORMAC's avatar
      BORMAC
      Copper Contributor
      May 08, 2024
      Great!
      Thank you very much - you solved my problem!

Resources