Carly Bond

Index & Match with an Array


I spend most of my day at work returning values from one worksheet to another or one list to another, for example, matching a customer name to a customer number.   This can be done by using a combination of the INDEX & MATCH functions (see example below)


This would result in:


However, if I wanted to return more than just the customer name, I would find myself re-writing the formula over & over again, changing the column reference at the end as many times as I needed, however, you can use an array function to help with this by following these instructions:

  • Next to your customer number select the number of columns you would like to return


  • Type in the following formula (make sure the absolute values are on the red & blue parts but NOT the green section)


  • DO NOT PRESS ENTER!!!! (This is very important)
  • Click Ctrl + Shift + Enter (This should enter the results into all cells selected & you will notice the formula has been surrounded with {} brackets


  • Now drag down the formula