In this tutorial we are going to look at a typical INDEX / MATCH combination formula but with a twist that concatenates both the lookup values and the lookup arrays, making it an array formula.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here we have a data range that has 8 divisions and 16 Year/Quarter combinations of sales data:
I’ve set up an area whereby I can change the Division or Year/Quarter using Data Validation drop down lists, and Excel will give me the appropriate value, using an INDEX/MATCH formula combination in cell B12:
The formula used is:
This is a typical use case for INDEX/MATCH where the range is defined and we use the MATCH functions to define the row and column, at which point the intersection gives us the correct value. If you want to learn more about INDEX/MATCH, go to my website and search on that term and you will find a few tutorials explaining this very powerful function combination.
But what if we have this scenario, where the column headers consist of two rows, one for the Year and one for the Quarter:
And the area set up for the INDEX/MATCH formula also separates the Year and Quarter:
Well, the key is to CONCATENATE the lookup values of the MATCH function along with CONCATENATING the ranges or arrays that the MATCH function searches, and then enter the formula using CTRL + SHIFT + ENTER to make it an array formula.
Here is the formula we used in row 14:
Notice the curly brackets around the formula. These can’t be manually entered, they are inserted by Excel when you enter the formula using Ctrl + Shift + Enter, indicating that it is an array formula. An array formula is defined as “…a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result.”
For the INDEX function, the syntax is:
=INDEX(array, row_num, [col_num])
…and each component is as follows:
array – $B$3:$Q$10
row_num – MATCH($A14,$A$3:$A$10,0)
col_num – MATCH(B$12&B$13,$B$1:$Q$1&$B$2:$Q$2,0)
The syntax for the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
…and each component for the column number is as follows:
lookup_value – B$12&B$13
lookup_array – $B$1:$Q$1&$B$2:$Q$2
match_type – 0
So with the MATCH function used to generate the column number, notice how we CONCATENATED the lookup value and the lookup array with an ampersand (&). In this way, Excel will join the two cells for the lookup value and search through all the combinations in the lookup array to find the proper match, PROVIDED that the formula is entered as an ARRAY formula using CTRL + SHIFT + ENTER.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!