In business, it’s always important to be efficient, and one of the most underutilized tools in Excel – or in any spreadsheet program, for that matter – is the Vlookup formula. Using Vlookup, you can import information from other worksheets and even other workbooks into your current worksheet without having to copy and paste cells manually. Excel Vlookup: The Most Powerful Formula You’re Not Using
In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
This saves time, which saves money! Excel Vlookup: The Most Powerful Formula You’re Not Using
Table of Contents
What is a Lookup?
A lookup is a function in Excel that allows you to return data from a different row or column by using the item you are looking for as the key. It’s helpful for setting up an organized, dynamic spreadsheet that can automatically update itself without having to delete cells, manually enter new data, or create formulas.
Basic Lookups
The Excel VLOOKUP function is one of the most powerful functions in all of Excel, but it’s often unused by spreadsheet users. This week I’m going to show you how powerful this function really is, and why it should be a staple in your spreadsheet tool belt.
If you don’t know what a lookup or index/match is, take a quick moment to review my previous blog post on the topic before reading on.
Advanced Lookups
Often, we just need to find an item in a table that is sorted by column. For instance, if you want to find the monthly sales figures for individual items, you could use a lookup formula (e.g., VLOOKUP) to return the corresponding values from a second table.
From VLOOKUP to HLOOKUP, INDEX & MATCH.
VLOOKUP is the most popular function in Excel, but there are many different functions that can be used to search for information. This post discusses some of the other formulas you can use, such as HLOOKUP, INDEX & MATCH and IFERROR. We will also discuss how these formulas work together with VLOOKUP to give a more complete picture of what’s going on behind the scenes.
About Table Names in Lookups
When referencing a table name in the data table you are looking up on, use one of the following in place of Table1, Table2, etc.
(i) TableName=TableName OR TableName=’Sheet’ OR Tablename=Sheet
(ii) Sheet!TableName = TableName OR Sheet!TableName=’Sheet’ OR Sheet!TableName=Sheet (iii) The Worksheet Name should be left blank when referring to a sheet within the same workbook and it is already named; otherwise enter the full path of that sheet relative to your current workbook location
Removing Duplicates with Index/Match
Do you need to remove duplicates in your Excel data? This can be done quickly and easily with the Index/Match formula. There are two different functions that make up this magical formula: Index, which locates a specified value within a range, and Match, which looks for either partial or complete match of a specified value. When put together, the result is both powerful and efficient for solving any duplicates problem in your spreadsheet.
Nested LOOKUPs
LOOKUP is one of the most powerful formulas in Excel, and can also be one of the most confusing. Nested LOOKUPs are a more advanced version of what you’ve been doing up until now, with one LOOKUP formula taking information from a cell in your original data set and using it to find another piece of information.
An example would be if you wanted to find the name that corresponded with an age in the same column.
Putting it all together – Solving our Leads Scorecard Problem
The most powerful formula in Excel is the VLOOKUP function. Use it when you have a list of items and need to find which row the corresponding value is on, based on a known column. Here’s how to solve our Leads Scorecard problem with this powerful tool. First we’ll create a table called Leads with three columns (Name, Lead Status, Score) and six rows (Jane Doe (not active), John Smith (active), Jane Green (active), etc.).
Start by typing Name into the first cell in Column A. Next type Lead Status into Column B and then type Score into Column C.
