Written by Kasper Langmann, co-founder of Spreadsheeto
If you keep meeting information in Excel, VLOOKUP can help you get information quickly.
The function seems complicated, but once you get used to it, it’s actually quite straightforward. Let’s take a look at a few ways you might use it in your Excel calendar.
(You can also combine these methods with data validation dropdowns to speed things up.)
1. Find Meetings with Specific Attendees
Let’s say you have a list of names in column A and meeting dates in column B. You can give the VLOOKUP function a name and get the date that you have a meeting scheduled.
If we want to know when we have a meeting with David Smith, we’d use this formula:
=VLOOKUP(“David Smith”, A1:B100, 2)
Excel looks for the text “David Smith” in cells A1 through B100, then returns the corresponding value from the second column.
You can use this with any vertical list of information; names, dates, meeting rooms, notes, catering instructions, and so on.
2. Find Attendee Information
If you have a list of meeting attendees in one column and additional information in other columns, you can use VLOOKUP to find that information with the attendee name.
Let’s say we have attendee names in column A, and then three columns of information: home city, company, and email address.
Want to find Antonio Suarez’s email address?
Use this formula:
=VLOOKUP(“Antonio Suarez”, A2:D100, 4)
Excel will find Antonio Suarez in column A, then give you the corresponding information from column D.
3. Get Speaker Information
If you’re working with speakers at events, they’ve probably sent you a lot of information. Bios, presentations, handouts, and other materials can be difficult to track in your inbox. Why not use a spreadsheet and VLOOKUP instead?
A spreadsheet with speaker names in column A, followed by links to bios, presentations, and handouts is a great method for this.
To get Jeff Rogers’ presentation, you’d use this formula:
=VLOOKUP(“Jeff Rogers”, A2:D50, 3)
This tells Excel to find Jeff Rogers and return the value in the third column of the array—which, in our case, is a link to his presentation.
It takes some time to get set up, but it’s faster than managing all of this information in your inbox.
4. Pull Information from Another Sheet
In most cases, you’ll use VLOOKUP to find information from the sheet that you’ve typed the formula into. But you can also use it to look up information from another sheet in the same document.
When you’re selecting the table array (the second argument), click and drag to select an area on another sheet.
Or you can use the exclamation mark to tell Excel which sheet you’re referencing. Here’s an example:
=VLOOKUP(“conference room”, Sheet2!B3:D41, 3)
That will look for “conference room” in the specified range on sheet 2, instead of the sheet you type it into.
About Kasper
Kasper Langmann is the co-founder of Spreadsheeto, the place to learn Excel step-by-step. Kasper is a certified Microsoft Office Specialist and lives in Copenhagen, Denmark. Kasper has taught Excel to 1000s of people from all over the world through his tutorials and free Excel course. Head over and try the training yourself!