Lookup functions for efficient data retrieval - VLOOKUP

Lookup functions are tools used in Excel to search for specific data within a table or range of cells and retrieve related information based on certain criteria. They are particularly useful when you have large sets of data and need to quickly find and extract specific values or perform data comparisons.

In this tutorial, we look at: VLOOKUP

VLOOKUP in Excel helps you find information from a table by looking up a value you specify. You tell it what you're searching for, where to look (the table), which column has the data you want, and it gives you the related info. It's like asking Excel to find a name in a phone book and tell you the corresponding phone number.

EXAMPLE of how to use VLOOKUP

You are an administrative assistant for a company that organizes events. You are responsible for managing the guest list for an upcoming conference. The guest list includes attendees' names, email addresses, and their affiliations (e.g., company or organization). The assistant receives a list of attendees from the registration team, but it's missing some crucial information: the attendees' companies.

To complete the guest list, you need to add the company names for each attendee. Fortunately, you have access to another spreadsheet provided by the sales team, which contains a list of all the companies that have registered for the conference, along with their corresponding email domains.

Using VLOOKUP, the administrative assistant can quickly match the email domain of each attendee with the corresponding company name from the sales team's spreadsheet. By setting up a VLOOKUP formula that searches for the email domain in the sales team's spreadsheet and returns the corresponding company name, the assistant can efficiently fill in the missing information in the guest list.

In this scenario, VLOOKUP saves the administrative assistant valuable time by automating the process of updating the guest list with the attendees' company names, ensuring that the list is complete and accurate for the upcoming conference.

In this example, the administrative assistant needs to use VLOOKUP to fill in the "Company" column in the guest list based on the email domain provided in the "Email Address" column, using the information from the sales team's company list.

st values respectively within a range of cells. A

Assignment

1. Create a spreadsheet to display this data, merging the two lists into one, with the "Guest List" data starting from cell A1 and the "Sales Team's Company List" data starts from cell D1.

2. Here is the VLOOKUP formula we can use in cell C2 of the "Guest List" to fill in the company name:

=VLOOKUP(RIGHT(B2,LEN(B2)-FIND("@",B2)), Sales_Team_Company_List!$D$2:$E$4, 2, FALSE)

Here is the break down of the formula:

RIGHT(B2,LEN(B2)-FIND("@",B2)): This part extracts the email domain from the email address. It starts from the right of the email address (cell B2) and takes the number of characters equal to the length of the email address minus the position of the "@" symbol.

Sales_Team_Company_List!$D$2:$E$4: This specifies the range where the VLOOKUP function should search for the email domain and corresponding company name.

This specifies that the VLOOKUP function should return the value from the second column of the specified range (which contains the company names).

FALSE: This ensures that the VLOOKUP function finds an exact match.

Drag this formula down for all the rows in the "Guest List," and it will automatically fill in the company names based on the email domains.

Submit your answer to actira.tt@gmail.com of the relevant columns (A, B & C)