Learn how to turn a list of names, products, or cities into a data matrix. Cross-check each item to create a table like the one below 👍

Distance Between Cities

This example shows distances between cities in Europe. Notice that the order of cities in the columns doesn’t matter—one entry is enough 😉

Step 1: Extract Unique City Names

First, focus on the City 1 column. Since City 1 and City 2 have the same data, we only need one. Use the UNIQUE function in Excel 365 and 2021 to remove duplicates, and the SORT function to organize them.

Step 2: Transpose the Column

Next, use the TRANSPOSE function to change the result from vertical to horizontal. The # symbol returns all cells from the UNIQUE function.

Step 3: Create the Matrix Formula

Now, we build the main formula to turn a list into a matrix. It compares cities in the source table and outputs results.

1. ((tbl_Dist[City1]=F$1)*(tbl_Dist[City2]=$E2))
   • This checks if City1 in tbl_Dist matches F$1 and if City2 matches $E2.
   • tbl_Dist[City1]=F$1 and tbl_Dist[City2]=$E2 create TRUE/FALSE arrays.
   • Multiplying these arrays gives a 1 when both are TRUE, otherwise 0.
2. ((tbl_Dist[City1]=$E2)*(tbl_Dist[City2]=F$1))
   • This checks for the reverse city order (City1 is $E2, City2 is F$1).
   • This ensures distance is found regardless of city order.
3. ((tbl_Dist[City1]=F$1)*(tbl_Dist[City2]=$E2))+((tbl_Dist[City1]=$E2)*(tbl_Dist[City2]=F$1))
   • The + operator combines the results, giving 1 if either condition is TRUE.
4. MATCH(1, …, 0)
   • This function locates the row with matching cities (F$1 and $E2).
   • MATCH finds the first 1 in the array, identifying the matching city pair.
5. INDEX(tbl_Dist[Distance(km)], …)
   • INDEX returns the distance from tbl_Dist[Distance(km)] at the MATCH position.
6. IFERROR(…, “-“)
   • IFERROR shows a hyphen (“-“) if no match is found, instead of an error.

Copy this formula across each cell in the Distance Matrix.

• We applied a color scale to highlight distances using conditional formatting.