Feel free to download our Exercise file and practice along with the video. And don't worry, there is also a Solution file with all the formulas.
We have more explainers on advanced Excel functions you might be interested in - INDEX and MATCH; VLOOKUP COLUMN and ROW; INDIRECT, VLOOKUP and INDIRECT.
We’ve seen several function combinations so far.
In this lesson, we’ll present another one that can be useful.
Suppose you’d like to create a table with the number of people working in Management and Admin personnel for company D and E again.
We can use MATCH to define the column number we need in the VLOOKUP function.
I am sure you now know the first part of the VLOOKUP function by heart. This will be our lookup value, and we will fix its column reference. The entire table above is the lookup array.
Cool.
This is when MATCH comes in play. The lookup value of MATCH will be this cell.
When I copy and paste this function, I’ll need this reference to change only when I move to the right, along the horizontal axis, and not to change when I move down. Therefore, I will anchor its row reference.
Great!
In the next step, we have to define the area, or the lookup array, where Excel will try to find the lookup value. In our case that means company D. Therefore, I will select the header of the source table and fix it! It is not supposed to move when we copy and paste the function.
And this pretty much does it. Let’s press Ok and see the result of the function we just created.
All right, it worked okay for the number of managers in Company D. Let’s apply this formula to the other three cells in the table.
It worked.
Therefore, one could combine VLOOKUP and MATCH in the same formula.
From all that you’ve learned until this moment, you would probably agree that nesting a match function within a VLOOKUP differs from nesting COLUMNS within VLOOKUP, though. Now, if I replace the name of a company with another one, the number of workers in the management and the admin personnel would change accordingly.
This is not the case with the COLUMNS function, as it works fine only for adjacent companies in the source table.
We hope this explainer was useful to you. Next, you can watch our video explainer about INDEX and MATCH: application of the two functions separately and combined and take our Introduction to Excel course to enhance your skills.
Next Video: Population vs Sample
