So far in our examples, the INDEX function has used an array that is either one column or just one row. This time, we're going to be referencing data that spans several rows and columns. For this example, we have cinema data with multiple cinemas and various ticket types, such as a standard seat, a 3D cinema, a VIP seat, and a sofa seat. The prices differ depending on the cinema and the ticket type.
In this example, we're going to use INDEX and MATCH to return the Notting Hill VIP seat price. We can see that Notting Hill and the VIP seat are here, so the price is £11. To achieve this, we'll use dropdowns. I'll first select "Notting Hill" as the cinema and "VIP seat" as the ticket type.
Next, I'll input the MATCH functions. I'll start with the row match, which will be used as the row coordinate in the INDEX function. My lookup value will be "Notting Hill," and the lookup array will be the list of cinema names. I'll set the match type to zero for an exact match. This looks up "Notting Hill" in the list and returns that it's in the second row of the array.
Now, I'll do the same for the columns. My lookup value will be "VIP seat," and the lookup array will be the ticket types. Again, I'll set the match type to zero for an exact match. It tells me that the "VIP seat" is the third column across in the array.
Now that I have the row and column numbers, I can use the INDEX function. I'll select all the prices as the array, then input the row number (which is 2) and the column number (which is 3). After closing the brackets, we get the £11 price that we saw earlier. This INDEX function is referencing the entire array, looking two rows down and three columns across to return the price of the Notting Hill VIP seat.
Finally, I can take the next step to embed the MATCH functions within the INDEX function. I'll start by typing out INDEX and referencing the array. I'll turn the next part into a text placeholder by adding an apostrophe at the start. Then, I'll go back to the MATCH function, copy it, and paste it into the INDEX function. I'll do the same for the second MATCH function, copy it, and paste it at the appropriate place in the INDEX function. After removing the apostrophe, the formula returns to its normal state.
When I close the brackets, we again get the £11. What has happened is that the first MATCH function returned 2, and the second MATCH function returned 3. I've embedded these values into the INDEX function.
Now, if I change the cinema to "Stratford," the row number changes to 6 because Stratford is the sixth cinema down the list. If I change the ticket type to "3D," the column match changes to 2 because 3D is the second ticket type across. The function now looks up row 6 and column 2, which gives us £12.50. The same principle applies in both cases where the INDEX function has the MATCH functions embedded within it.
