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
