The index match Excel formula combination allows the user to lookup a single or multiple value(s) against a secondary table to display the value based on matching criteria between the two tables.
- =INDEX(array,row_num,[column_num]) - we are using the array form of the index formula in this post.
- =MATCH(lookup_value,lookup_array,[match_type])
The index match formula combination will be used in this post to lookup unique row records based on warehouse, location and item number and display on the consumption table the inventory on hand for that particular warehouse, location and item number. Matching based on multiple criteria allows us to find the exact unique row where 3 different conditions are met. This is done through using the match formula which provides us with a row number. The index formula specifies which column to find the value we would like to display on our primary table.
How to Use the Index Match Formula Combination
(1) To begin, in order to successfully use the index match formula with multiple lookup criteria, you need to have two tables where the record is unique based on the combination of multiple fields. In our case, in order to make a record unique, the on hand quantity which is the value we are trying to populate in the consumption table, we must look up our record based on the warehouse, the location and the item number. The reason for this is if we were to only look up item number we would have 8 different results, if we were to only look up the item number and the warehouse we would still have 2 different results and finally in order to get only 1 result, we must match by the warehouse, the location and then the item number in order to make the record result unique.
(2) Now that we've established our data requirements for this exercise our goal will be to get the quantity in the second table (inventory table) into our first table (consumption table).
(3) In order to do this we will use the index match formula as mentioned above. The index match formula will be input into cell F2 of the consumption table (first table). See below diagram for a detailed explanation of how to use this formula (each step # in the table of the diagram corresponds to a number within consumption and inventory table). Note that once the entire formula is entered, before leaving the cell, type Ctl+Shift+Enter and this will ensure that the array brackets are added. These are necessary in order for the formula to work when using an array formula.
(4) So let's go over the formula quickly:
={INDEX(Quantity Field array (inventory table), MATCH(warehouse&location&item (consumption table), warehouse&location&item arrays (inventory table), 0 (exact match type)))}
The dollar sign ($) in front of the cell row and column references indicate the row or column will stay constant (i.e if we have $A2, this means that the column references will remain as A but the row will change as there is no $ value sign in front of it).
(5) Because I've created a table out of my ranges, the formula from cell F2 will automatically be copied to all other cells within the quantity field column on the consumption table as shown below. If you are not using a table, double click the auto fill option in the bottom right hand corner of the cell where the formula was input.
There you have it. You've successfully merged two tables together using the Index Match formula based on multiple look up criteria.
What would you use this formula for?
Download the Excel Workbook
Free Excel File Download!The Excel file is indeed free. When you click the 'GET IT FREE' button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you'll need is your email address!
Trackbacks/Pingbacks