Carly Bond

Index & Match with an Array

Excel

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)

Index&Match1

This would result in:

Index&Match2

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

Index&Match3

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

Index&Match4

  • 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

Index&Match5

  • Now drag down the formula

Index&Match6