With XLOOKUP , it is now possible to return multiple values from a single function. However; when these values are returned, they show in multiple columns. If we want to have them appear by row, we have to use the XLOOKUP / TRANSPOSE combination.
By using this multiple function combination, we can return results by row – not just by column. This allows us to reduce the number of times that we need to use the XLOOKUP function; we don’t have to do a separate formula for every line!
Multiple Matching XLOOKUP Refresher
As a quick reminder, unlike VLOOKUP and INDEX / MATCH, the XLOOKUP function is able to return multiple columns. This can come in handy when you are looking to return more than one value!
This is accomplished when you add more than one column to your Return_array.
However; there is currently no parameter within XLOOKUP that allows results to be returned by rows!
The TRANSPOSE function
Since there is no functionality within XLOOKUP to return values by row, the TRANSPOSE function can be used.
According to Office Support:
The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa.
https://support.office.com/en-us/article/TRANSPOSE-function-ED039415-ED8A-4A81-93E9-4B6DFAC76027
If we wanted to list all the column names by row, one way to get these values would be to copy the values and paste transposed. The major disadvantage of this method is that the cells are pasted as values.
With the TRANSPOSE function, all you need to do is select the columns and they will be provided as rows!
Prior to the most recent version of Office 365 (February 2020), in order to use this function, you needed to press CTRL-SHIFT-ENTER to use this function. This functionality is no longer needed in Office 365.
If you have XLOOKUP, you don’t need to press CTRL-SHIFT-ENTER.
Combining XLOOKUP and TRANSPOSE
When we return multiple values with XLOOKUP, we are returning an array. This array can be transposed, using the TRANSPOSE function!
First, use the XLOOKUP function to return multiple rows, just like in the example above.
Next, put the XLOOKUP formula inside a TRANSPOSE formula. This will return all the values that were returned by the XLOOKUP by row instead of by column!
Conclusion – Limitations
When combined with the XLOOKUP function, the TRANSPOSE function allows for multiple values to be returned by row.
A major limitation is that while the XLOOKUP function can return multiple columns, the columns need to be side by side.
In the example we used above, multiple XLOOKUP formulas needed to be used, as the Name field was in the middle of the dataset.
In order to not return the name value (and have duplication), 2 XLOOKUP formulas were required in order to provide all the values from the table.
This can be resolved by moving the name to the beginning of the table, but this would eliminate one of the main benefits of XLOOKUP, the ability to search both left and right of the search column!