Moving Beyond Basics: Nesting VLOOKUP and MATCH Functions

Nesting a MATCH function within a VLOOKUP can enable you to eliminate manual adjustments to your VLOOKUP functions when working in spreadsheets with Excel.

For reference purposes you can download the sample Excel file by clicking here.

Before you try these instructions, make sure that you have a mastery of the basic VLOOKUP formula; you can reference this earlier post.

Open the sample file and insert a new tab. (You can hold shift and press F11 to do this quickly)

Right click the new tab and rename it “Summary”. Your screen should look something like this:

Go to the “Data” tab and copy all the names of the countries that begin with “A”, paste the values on your "summary" sheet (starting in cell B6) add a COUNTRY label to the top of the list, then go one cell up and one cell right and type “POPULATION”. Under population add years going from left to right, from 2011 to 2020. You should now have something similar to below.

In cell C6 type in the following:

=VLOOKUP($B6,Data!$B$3:$L$201,MATCH(Summary!C$5,Data!$B$3:$L$3,FALSE),FALSE) 

If you took a shortcut and cut and paste the formula, you will have to change the font color for the result to show.

To break down the formula in order to understand what was just entered, refer to the picture below.

Your sheet should look like the picture below.

Highlight C6 and copy it (use ctrl+C, or right click and select Copy), then highlight cells C6 through L15 and paste (ctrl+V, or right click and select Paste). Your sheet should now look like the picture below.

Now you should know the basics of nesting the MATCH formula within the column reference section of a VLOOKUP. This will make your spreadsheets more adaptable and easier to update.