!
Excel Skill #3: How to Do a VLOOKUP
(Mac Version)
1. Take the pivot table you made in the last video, copy everything from “Title” down to the last row
of data before “Grand Total”, and paste the values into a new sheet. (Tip: You can hold down the
shift key and use the keyboard shortcut “Fn-Down Arrow” to quickly extend your selection down
the page. You can also use the shortcuts “Command-C” and “Command-V” to copy and paste
respectively.)
2. For this example, “Title” is our unique identifier, and our objective is to match each title to its
author. To do this, start by going to the first empty cell in the first row (cell E1, in this case) and
label it “Author.” Next, select the first cell in your newly created “Author” column (cell E2) and click
on the formula button (
fx
) up in the top navigation.
3. Double click on VLOOKUP in the Formula Builder window. (Note: You can simply type “V” in the
function search field to find VLOOKUP, or you search for it in manually – it’s under the Lookup &
Reference category.)
4. When you see the lookup_value field appear, click on cell A2. Alternatively, you can type “A2”
into the empty lookup_value field. This is the value that you’re trying to find a match for.
5. Next, Excel will ask you for the table_array, which is the place where Excel can find your data.
For this example, your data is in the “Pages” tab of your workbook. So, head to the “Pages” tab
and select the columns you want Excel to sort through. For this example, you can select columns
B through K. An easy way to select these columns is by clicking on the “B” above the first row
(which selects the entire B column), holding shift, and using your arrow keys to select all of the
other columns, including K. Alternatively, you can type “Pages!B:K” into the empty table_array
field.
6. Next, Excel will ask for a col_index_number, which is the number of the column in your table
array where a matching value can be returned. Since “Author” – the value we’re searching for --
is the 10th column from the left in our table array, type “10” into the empty col_index_number
field. (Note: This might seem counterintuitive, since Excel labels columns using letters, not
numbers. But trust us: for col_index_number, you want to use the number of the column.)
7. Finally, when range_lookup appears, type “FALSE.” “FALSE” indicates that you want an exact
match, as opposed to an approximate match. (Note: The finished formula will read
=VLOOKUP(A2,Pages!B:K,10,FALSE) in the formula bar.)
8. Hit enter, and your first author will appear. To carry the function down the entire column, select
the cell that has your first author in it (E2) and double-click on the little box in the bottom corner.
Click here to learn more about using the VLOOKUP function in Excel >>