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

Carly Bond

is a regular guest blog poster here at MikeSel.info, and also a blog poster on her own site, with a wealth of experience in VBA and Microsoft Excel - Carly is constantly expanding her knowledge and is actively learning PHP

%d bloggers like this: