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

Mike Hudson

Mike Hudson is a Lead Cyber Security Analyst living and working in Kingston Upon Hull. With extensive experience in Microsoft and Apple technologies, ranging from desktop OS’s to Server OS’s and hardware. By day working as part of an infrastructure team, and by night ridding the world of IT issues through blog posts..

%d bloggers like this: