Using INDEX and MATCH with an Excel Table is wonderfully straightforward. To illustrate, I’ll build INDEX and MATCH formulas that do the same thing as the VLOOKUP formulas already on this worksheet. First, to recap, these VLOOKUP formulas currently retrieve information from Table1 based on the ID provided in K4. Each formula uses the lookup_value from K4, the entire table for table_array, and the MATCH function to figure out what column number to return, based on column names in column J. The last argument is FALSE to force an exact match. If I change the ID, the formulas return information for a different employee. I’ll start off by creating a copy of this structure to the right, then I’ll remove the formulas. A simple way to build out an INDEX and MATCH formula is to start with INDEX only and hardcode the row and column numbers. For array, I use the entire table. For row_number, I hardcode 5, since ID 622 corresponds to row 5 in the table. For column_index, I use 2, since first name is the second column. With this information, INDEX correctly returns “Jon”. If I copy the formula down and change the column number to 3, I’ll get Jon’s last name. Now all I need to do now is replace the hardcoded values with MATCH. To get the row_number, I use MATCH with N4 as the lookup_value, I need to lock this because I don’t want this to change. The array is the ID column of the table. And match_type is zero, for exact match. The row number now works. To get column_number, I use MATCH again. This time, the lookup_value comes from column M. The array is the table header. And match_type is again zero, for exact match. And now I have a set of working formulas, that work just like the VLOOKUP version. However, INDEX and MATCH offers one big advantage. Unlike VLOOKUP, the lookup_value doesn’t need to be the first column. If I move the ID column in the table, the VLOOKUP formulas stop working, but the INDEX and MATCH formulas continue to work just fine.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
