I recently had to match a master list with another list to see what terms matched in both lists in Excel and keep them in order. Another thing I wanted to do was to make sure if there wasn’t a match in my second list with the master list of words, that a space was inserted in the column (representing no match). Here are the steps I took to make my list in Excel (I am working in Excel 2010).
- Copy and paste your master list (the list with all the terms you are checking) into Column A.
- Copy and paste the list of words you want to see a match for against the Master list into Column B.
- To put the two list into alphabetical order, you can select the two columns and go to Sort–> A-Z.
- We will use column C as the column to match our column B data and put spaces for none matches with Column A (Master list)
- Select the first cell in Column C and type the following formula in the formula textbox: =IF(ISNA(MATCH(A1,B:B,0)),””,A1)
- Copy that formula all the way down the column (stopping at the last master list word).
- Now Column C will be a sorted matched list to Column A with spaces if there isn’t a word match.
I hope this helps!