Forum Discussion

Eliane's avatar
Eliane
Copper Contributor
Dec 30, 2024

Sort cells in a table with Xlookup

Hello everyone :)

I have a little problem..

For my work I have to create a table “Project planning”, which is fed with information from another table “Customer export”. Using the “Xlookup” function, all important customer information from the “Customer export” table now appears in the target table “Project planning”.

If I work in the “Project planning” table and want to sort it in a user-defined way, nothing happens. I have made sure that all columns are defined and formatted correctly (e.g. text, number format, date, etc.), but when I want to sort the table alphabetically by customer name, nothing happens. If I sort in the original table “Customer Export”, it logically sorts in the “Project Planning” table as well, but this is extremely impractical and inadequate.

The thing is that I have already created several tables with Xlookup and so far it has never been a problem to sort user-defined in these tables.

Does anyone know what the problem is here?

Many thanks in advance!

Excel on Mac

1 Reply

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jan 01, 2025

    Try below:

     

    1. Convert to Values: The "Xlookup" function likely returns dynamic values, which might be causing the sorting issue. Try converting the lookup results to static values:
    • Select the relevant columns in the “Project planning” table.
    • Copy them (Ctrl+C).
    • Right-click and choose "Paste Special" > "Values".
    1. Recreate Table: Ensure the “Project planning” table is defined as an actual table (Insert > Table), which supports sorting and filtering functionality:
    • Select the range with your data.
    • Go to the "Insert" tab and click on "Table".
    1. Check for Hidden Characters: Sometimes, hidden characters or spaces can mess with sorting. Ensure there are no leading/trailing spaces:
    • Use the TRIM function in another column to remove any excess spaces.
    • Copy the cleaned data and paste it back as values.
    1. Data Consistency: Verify that all the data types in the “Customer export” table and the “Project planning” table are consistent. Mixed data types can confuse sorting mechanisms.
    2. Simplify Sorting: Ensure the sorting is straightforward:
    • Select the entire table by clicking any cell within the table.
    • Go to the "Data" tab and click "Sort".
    • Choose the column to sort by (e.g., Customer Name).

Resources