I was originally going to integrate this tutorial as part of the Creating A Parts Lookup Spreadsheet Part 2, when I realized that there is so much to cover on array's that it would take away from the lookup tutorial. Here we will examine how array's are very handy. They work similar to a vlookup, but are not limited to looking up off one column, but several.
- INDEX – Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) form and the reference form – Read more here
- MATCH – Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself – Read more here
This tutorial was written for Excel 2007, but will work in most versions of Excel.
Find more information about Microsoft Excel functions at the Microsoft Office website.
An array is a grouping of data. The best visual representation of an array in Excel, that I can think of, would be the game Connect Four.
The board is laid out like a grid and the checkers are slid down a slot. Imagine that each checker contains information. Looking at the grid from left to right, the left most column might be a first name, second column a last name, third a birth date, and so on. The first column would then be an array of first names, the second would be an array of last names, and the third column would be an array of birth dates. Each checker is an element of the array in that column.
|1||First Name||Last Name||Birthday|
In Excel, we can find a birthday by writing a function to look at the first two columns to match names to get the birthday we are looking for, like finding two red checkers sitting side by side.
We start with 3 columns with the first and last name along with the birthday. Place these in a spreadsheet called "names". Fill in some test data.
On the second tab, labeled "lookup", type in a test first and last name in separate cells. In the third cell we will start writing the MATCH function. We will start with just looking for the first name.
The second part of the function will be the array of information to look up. In this example we will grab the whole column. I prefer using named ranges as they are more reliable, but this is a quick build.
The third item in the function is the match type, place "0" in this field to pull an exact match. I have had odd results when using this function without placing the "0" in the function, be sure to place the "0" in the function.
Here is a breakdown of the function.
=MATCH( cell with name to look up , column to find name , 0 )
Which gives us the row where the fist instance that name appears.To add a second column to look up on we will use "&" to connect the data together.
=MATCH( cell with first name to look up & cell with last name to look up , column to find first name & column to find last name , 0 )
As can be seen, when pressing enter we get a #VALUE! error. When working with arrays, leaving the cell must be done in a specific way. While editing the cell, press Ctrl + Shift + Enter. This gives us the row number where the first and last name match.
Now we will use the index function to find the birthday. The first part of the function is selecting the column of birthdays. Insert the match function that was created into the index function. As before, while editing the cell, press Ctrl + Shift + Enter.
=INDEX( column with the birthdays , MATCH( cell with first name to look up & cell with last name to look up , column to find first name & column to find last name , 0 ) )
The information displayed looks like a series of numbers, not a date. That can be fixed easily by formatting the cell and selecting the "Date" format.
Try changing the name and see how the date changes. Arrays are very useful, this is a very simple example of how to use them.