Series: Pharmacy-Specific Microsoft Excel Concepts and Data Manipulation – Part 1

Part 1: Importance of data format and the INDEX and MATCH functions, the single most important tool in my daily excel use

Foreword: This series is intended to explain concepts and tools in pharmacy and Excel that I utilize frequently in my own job as an Epic Willow (Pharmacy) certified analyst. Everything shared is knowledge I have acquired in the process of analyzing pharmacy data and solving problems encountered in the real workspace.  Please be aware this may be a narrow lens and I do not claim to be an expert. I welcome all additional knowledge and criticism during this series.

What format is your data in and why is this important?

The first thing I always check before manipulating data is its format. This is important because the format determines how you can transform the data. On the right are important data formats in regards to pharmacy data.  Here are some pearls I have learned along the way:

• Data must be non-text (general is okay) in order to perform general calculations on it
• Dates must be in date type formats for proper chronological analysis
• Text formatting may be necessary when dealing with leading zeros (NDCs)

How do you check your data format? Here are two ways:

Via the Ribbon:

First, highlight the cell you’re interested in by left clicking. If you have many cells of the same type you can select the entire column as well. Once your selection is highlighted in the excel table, the type of data should show up where the red box is below.

Via right click and Properties:

You can also highlight your data as mentioned above and right click, a right click menu should pop up with an option called “Format Cells”. Select the option for “Format Cells” and the following window should pop up indicating the type of data you have highlighted.  I prefer using this method to change the format of my data – usually by selecting a different category.

One very important caveat to above is if your data is numerical, as in the case with MRN, check what side of the cell it’s aligned to – is it on the left side or the right side?

If the data is left-aligned as above excel may still consider it as text. First, make sure you have changed it to general. If it is still left aligned, highlight the column and switch to the Data ribbon in order to utilize the text to columns function.  This will ensure your data is ready to be used in the formulas like the one we are about to go through.  The data should shift to the right hand side after doing this. (Just click finish after you click text to columns)

Index / Match: The most important tool to learn for a pharmacy analyst

INDEX/MATCH allows you to join two different sets of data with a common intersection point. It is actually two excel functions mixed together, the INDEX function, and the MATCH function. The combination of these two is superior to VLOOKUP because for VLOOKUP a portion of your data has to be in the left most column; INDEX/MATCH has no such limitations.  In short, if you have two different excel sheets with a common denominator like patient MRN, you can pull data from one sheet onto another and view it in a more convenient space.

Lets break down the essence of the INDEX and MATCH function in order to join two sets of data.

(Empty cell where you want your data) “= INDEX(COLUMN X,MATCH (CELL D, COLUMN A, 0))”. Anything inside the quotes is what you would type in the empty cell.

I will further break this down into its parts for ease of use. This formula needs the user to figure out three parts. The 0 in the formula should be kept as is, it tells excel that you want an exact match between both data sets.

Below you can see the explanation above in action. When the formula calls you to select a column, you click the top of the respective column and it will fill into your formula.

Once you have filled out the first cell, you can complete the rest of your column by dragging the fill handle (see link below).

https://support.microsoft.com/en-us/office/copy-a-formula-by-dragging-the-fill-handle-in-excel-for-mac-dd928259-622b-473f-9a33-83aa1a63e218

You are not done yet though.  Now your cells display the correct information, but they are actually stored as formula. You can verify this by left clicking one of the cells and seeing what displays in the formula box. In the picture above, I have selected the cell that says “Managed Care”, but to Excel this is actually stored as an INDEX/MATCH formula.

Having your data stored that way has many implications, the most important being that if you send this sheet to a colleague with the formula in the cells it will error out on their computer.  Excel does not know where the formula is pointing to since typically you would not send both sheets.

Copy all your cells, usually you can just copy an entire column, and then right click and paste values into the same column.  This will preserve your newly indexed and matched information.