How to average multiple vlookup findings in Excel?
For example there are many values matched the lookup value in your table, and you want to average all vlookup findings (target values) in Excel, how could you calculate the average quickly? This article lists three methods to solve this problem easily:
- Average multiple vlookup findings with formula
- Average multiple vlookup findings with Filter feature
- Average multiple vlookup findings with Kutools for Excel
Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
Actually, we don’t need to list all vlookup findings (target values) at all, and we can easily average all vlookup findings with the AVERAGEIF function.
Enter the formula =AVERAGEIF(A1:A24,E2,C1:C24) into a blank cell, and press the Enter key. And then the average of all vlookup findings has been calculated. See screenshot：
Notes:
(1) In above formula, A1:A24 is the column you will search for the lookup value, E2 is the lookup value, and C1:C24 is the column where you will average vlookup findings.
(2) You can also apply this array formula =AVERAGE(IF(A1:A24=E2,C1:C24)) into a blank cell, and press the Ctrl + Shift + Enter keys simultaneously.
Easily vlookup and sum/count/average lookup findings in Excel
Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch sum, count, and average based on duplicate items in one column (the Fruit Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below screenshot shown. Full Feature Free Trial 30-day!
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
In fact, we can apply the Filter feature to find out all values matched the lookup value, and then apply Kutools for Excel’s AverageVisible function to average all vlookup findings easily.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
Kutools for Excel – Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Select the column headers and click Data > Filter.
2. Select the arrow beside the column header where you will search for the lookup value, next only check the lookup value in the drop down list, and click the OK button. See left screenshot:
3. Enter the formula =AVERAGEVISIBLE(C2:C22) (C1:C22 is the column where you will average vlookup findings ) into a blank cell, and press the Enter key. And then the vlookup findings’ average has been calculated. See below screenshot:
This method will introduce Kutools for Excel’s Advanced Combine Rows utility to combine rows based on duplicates in one column, and then calculate sum/average/max/min/count matched values in another column. Pleased do as follows:
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!
Kutools for Excel – Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Select the range you will average all vlookup findings, and click Kutools > Content > Advanced Combine Rows. See screenshot:
2. In the opening Advanced Combine Rows dialog box,
(1) Select the column you will search for lookup value, and click the Primary Key;
(2) Select the column where you will average vlookup findings, and click Calculate > Average;
(3) Specify the combination or calculation rules for other columns. In our case, we select the Price column and click Combine > Comma.
3. Click the Ok button.
And then rows have been combined based on duplicate values in the column you will search for lookup value, and values in other columns have been combined or calculated (average, sum, max, min, etc.). See screenshot:
At this time, you can easily get the average of all vlookup findings.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel. Read more.
VLOOKUP is one of Excel’s most well-known functions. You’ll typically use it to look up exact matches, such as the ID of products or customers, but in this article, we’ll explore how to use VLOOKUP with a range of values.
Example One: Using VLOOKUP to Assign Letter Grades to Exam Scores
As an example, say we have a list of exam scores, and we want to assign a grade to each score. In our table, column A shows the actual exam scores and column B will be used to show the letter grades we calculate. We’ve also created a table off to the right (the D and E columns) that show the score necessary to achieve each letter grade.
With VLOOKUP, we can use the range values in column D to assign the letter grades in column E to all the actual exam scores.
The VLOOKUP Formula
Before we get into applying the formula to our example, let’s have a quick reminder of the VLOOKUP syntax:
In that formula, the variables work like this:
- lookup_value: This is the value for which you are looking. For us, this is the score in column A, starting with cell A2.
- table_array: This is often referred to unofficially as the lookup table. For us, this is the table containing the scores and associated grades ( range D2:E7).
- col_index_num: This is the column number where the results will be placed. In our example, this is column B, but since the VLOOKUP command requires a number, it’s column 2.
- range_lookup> This is a logical value question, so the answer is either true or false. Are you performing a range lookup? For us, the answer is yes (or “TRUE” in VLOOKUP terms).
The completed formula for our example is shown below:
The table array has been fixed to stop it changing when the formula is copied down the cells of column B.
Something to Be Careful About
When looking in ranges with VLOOKUP, it is essential that the first column of the table array (column D in this scenario) is sorted in ascending order. The formula relies on this order to place the lookup value in the correct range.
Below is an image of the results we’d get if we sorted the table array by the grade letter rather than the score.
It is important to be clear that the order is only essential with range lookups. When you put False on the end of a VLOOKUP function, the order is not so important.
Example Two: Providing a Discount Based on How Much a Customer Spends
In this example, we have some sales data. We would like to provide a discount on the sales amount, and the percentage of that discount is dependent upon the amount spent.
A lookup table (columns D and E) contains the discounts at each spending bracket.
The VLOOKUP formula below can be used to return the correct discount from the table.
This example is interesting because we can use it in a formula to subtract the discount.
You will often see Excel users writing complicated formulas for this type of conditional logic, but this VLOOKUP provides a concise way of achieving it.
Below, the VLOOKUP is added to a formula to subtract the discount returned from the sales amount in column A.
VLOOKUP is not just useful for when looking for specific records such as employees and products. It’s more versatile than many people know, and having it return from a range of values is an example of that. You can also use it as an alternative to otherwise complicated formulas.
Tip: Try using the new XLOOKUP and XMATCH functions, improved versions of the functions described in this article. These new functions work in any direction and return exact matches by default, making them easier and more convenient to use than their predecessors.
Suppose that you have a list of office location numbers, and you need to know which employees are in each office. The spreadsheet is huge, so you might think it is challenging task. It’s actually quite easy to do with a lookup function.
The VLOOKUP and HLOOKUP functions, together with INDEX and MATCH, are some of the most useful functions in Excel.
Note: The Lookup Wizard feature is no longer available in Excel.
Here’s an example of how to use VLOOKUP.
In this example, B2 is the first argument—an element of data that the function needs to work. For VLOOKUP, this first argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as “smith” or 21,000. The second argument is the range of cells, C2-:E7, in which to search for the value you want to find. The third argument is the column in that range of cells that contains the value that you seek.
The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument. In other words, leaving the fourth argument blank—or entering TRUE—gives you more flexibility.
This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches the cells in the range C2:E7 (2nd argument) and returns the closest approximate match from the third column in the range, column E (3rd argument).
The fourth argument is empty, so the function returns an approximate match. If it didn’t, you’d have to enter one of the values in columns C or D to get a result at all.
When you’re comfortable with VLOOKUP, the HLOOKUP function is equally easy to use. You enter the same arguments, but it searches in rows instead of columns.
Using INDEX and MATCH instead of VLOOKUP
There are certain limitations with using VLOOKUP—the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn’t built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.
This example shows a small list where the value we want to search on, Chicago, isn’t in the leftmost column. So, we can’t use VLOOKUP. Instead, we’ll use the MATCH function to find Chicago in the range B1:B11. It’s found in row 4. Then, INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The formula used is shown in cell A14.
For more examples of using INDEX and MATCH instead of VLOOKUP, see the article by Bill Jelen, Microsoft MVP.
Give it a try
If you want to experiment with lookup functions before you try them out with your own data, here’s some sample data.
VLOOKUP Example at work
Copy the following data into a blank spreadsheet.
Tip: Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap Text ( Home tab, Alignment group).
By James Archer 19 July 2021
Here’s how to use VLOOKUP in Excel to find data in complex tables
Knowing how to use VLOOKUP in Excel can be extremely useful when you’re dealing with huge tables. It’s not just like using Ctrl+F to search for a specific word or number: VLOOKUP searches a specific, user-defined range, and returns data associated with the lookup term as opposed to the term itself.
Say you need to find the price for a certain item, or want to find out which colleague is working on a specific project. As long as it’s organized by row, like any self-respecting Excel table should be, VLOOKUP can find the data you need.
Using VLOOKUP does require punching in a formula, but doesn’t demand expert algebraic knowledge. To demonstrate how it works as simply as possible, we’ll walk you through an example where we use VLOOKUP to find out a person’s working hours; as you’ll see in the images, it’s only a small table, but this process works (and is intended) for much bigger tables and ranges.
There are also a couple of terms to get familiar with. “Lookup value” is, effectively, our search term: the word, phrase or data that we’ll have VLOOKUP search for. “Return value” is like a search result: it’s the data that VLOOKUP will fetch and present, having found it sorted with the lookup value by row. For VLOOKUP to work, the column containing the lookup value should be to the left of the column containing the return value, so shuffle around your table if they’re not in the optimal order.
How to use VLOOKUP in Excel
1. Write the lookup value in one cell, then click on an empty cell adjacent to it.
2. In the formula bar, type “=VLOOKUP(“ without spaces.
3. Click on the cell containing the lookup value you entered. Again, this should be to the left of the empty cell you originally clicked. Note how the cell number — in our example, A10 — now appears in the formula bar.
4. In the formula bar, type “,” then click and drag to select the cells you want to search through. This creates the “range” within which VLOOKUP will find the data you want: the return value.
5. This is where it gets a bit tricky, since the process involves breaking with how Excel normally identifies columns with letters rather than numbers. Here, you need to mentally assign numbers to each column you have highlighted in your range: in our example, we’ve highlighted the A and B columns, and will number then as 1 and 2 respectively. If, hypothetically, you were to highlight the C, D and E columns, you’d number them 1, 2 and 3 respectively.
With the range highlighted, continue typing in the formula bar. This time, enter “,” followed by the “number” of the column that’s likely to contain the return value. For our example, although we’re looking for a name that matches one in column A, what we’re really looking for is the data in column B, so we’re going to type “2” after the comma.
6. Type “,” immediately followed by “FALSE” to find an exact match within the range.
7. Press the Enter key on your keyboard, and the return value data will appear in the cell you selected during step 1.
This covers how to use VLOOKUP on a basic level; just remember to keep your data organized so that the more easily memorable lookup value is to the left of the return value.
For more on getting the most out of Microsoft Office apps, check out our guides on how to how to sign a Word document and how to save a Word document as a PDF.
- More: The best laptops we’ve tested
How to vlookup and return matching data between two values in Excel?
In Excel, we can apply the normal Vlookup function to get the corresponding value based on a given data. But, sometimes, we want to vlookup and return the matching value between two values as the following screenshot shown, how could you deal with this task in Excel?
Vlookup between two values and return corresponding records in Excel
Kutools for Excel‘s LOOKUP between Two Values utility helps you to vlookup and return the corresponding records based on the given values quickly and easily without remembering any formulas. Click to download Kutools for Excel!
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!
Vlookup and return matching data between two values with formula
To get the matching data from a given value which between two values, the following formula can help you to finish this job as quickly as you need.
1. Please enter this formula into a blank cell where you want to output the result:
2. Then, drag the fill handle down to the cells that you want to apply this formula, and all the matching values between the values are extracted at once, see screenshot:
Notes:
1. In this formula: A2:A15 is the range of smaller values and B2:B15 is the range of larger numbers in your report, the E2 is the given value which you want to get its corresponding value, C2:C15 is the column data which you want to extract from.
2. This formula also can help you to get the correct result when your lookup values in a left column, see screenshot:
3. If the value doesn’t exist, you will get an #N/A error value.
Vlookup and return matching data between two values with a useful feature
If you have Kutools for Excel, with its LOOKUP between Two Values feature, you can quickly get the corresponding data between two given values.
Tips: To apply this LOOKUP between Two Values feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.
After installing Kutools for Excel, please do as this:
1. Click Kutools > Super Lookup > LOOKUP between Two Values, see screenshot:
2. In the LOOKUP between Two Values dialog box, please do the following operations:
- Select the lookup value cells and output cells from the Lookup values and Output Range section;
- Then, specify the corresponding items from the Data range section.
Note: If you want to replace the error value when the date doesn’t exist with other specific text, please check Replace #N/A error value with a specified value option.
3. Then, click OK button, the matching data based on the given value have been extracted as following screenshot shown:
In the first VLOOKUP Hacks post, we talked about how the 4th argument impacts the sort order. But, there is more to uncover about this 4th argument. So, let’s pick up right where we left off.
So far, we understand that the 4th argument tells Excel whether we are looking for a value between a range of values or an exact matching value. Its official name is range_lookup, and now it is time to dig into what it actually means.
I have prepared a video demonstration as well as a detailed narrative below for reference.
Video Demonstration
Detailed Narrative
When the 4th argument is TRUE, or omitted, it tells Excel to perform a range lookup. What exactly is a range lookup? It means you are looking for a value between a range of values. The fastest way to explain this is with an example and a picture.
Let’s suppose the sales manager created a sales incentive program for the month. He would like you to pay a bonus amount to the sales reps based on their sales for the month. He then provides you with the following table:
If a salesperson had sales of 1,200 for the month, you would easily find the bonus amount of 50. If a salesperson had sales of 12,345, you would be able to determine the bonus amount is 500.
When you are doing this manually, you aren’t looking for an exact matching sales amount. You are looking for a sales amount that falls between a start and end point.
That is EXACTLY what the 4th argument means! That is a “range lookup.”
So, when the 4th argument is TRUE, you are telling Excel to perform a range lookup. When the 4th argument is FALSE, you are telling Excel to find an exact matching value.
Note: You may see 0 used instead of FALSE in the 4th argument. Excel evaluates 0 as FALSE, and any non-zero number as TRUE.
Now that we have the overall concept down, let’s dig into the Excel details.
When we humans perform a range lookup, we love seeing both the start and end points. For example, in the sales bonus illustration above, there are From and To columns. Being able to see both sides of the range makes us feel warm and fuzzy. Content. Comfortable.
But, here is the hack: VLOOKUP only needs the From column!
The implications of this are important. So, let’s unpack them. First, here is an updated table that would work perfectly with VLOOKUP:
Here is how I like to think about VLOOKUP. I like to think about it operating in two stages. In stage one, it looks in the first column ONLY. It starts at the top, and goes down one row at a time looking for its matching value. Once it finds it match, then it enters stage two, where it shoots to the right to retrieve the related value.
So, when the 4th argument is TRUE (or omitted), it will look down the Sales column until it finds its row. Any sales amount that is >= 0 and = 1,000 and
Jeff Lenning
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
8 Comments
Thank you so much for this article. I use VLOOKUP all the time but haven’t been taking advantage of the range lookup capability. You’ve saved me at least two hours a month and that’s huge, especially during the month-end and quarter-end reporting rushes!
Welcome! And glad to hear about the time savings, that is what its all about 🙂
A few ideas
A. When I use the VLOOKUP Range based formula I think of it returning the largest pre-defined range value that is less than or equal to the input value.
B. The VLOOKUP Range example for the Sales Incentive situation above can be extended to provide additional capability and flexibility. Consider the following three columns:
1. Trigger X value (E.g. equivalent to the ‘Sales’ column above)
2. Starting Y Value at X (E.g. equivalent to the ‘Bonus’ column above)
3. Incremental Rate (the per X rate that applies after Trigger Value)
The combination of the three columns provides a configurable slope that applies after the trigger X+Y values, instead of just assuming a flat line after the ‘steps’ initial X + Y values.
Assuming the 3 columns above are in Table1, the formula used becomes:
=VLOOKUP(B7,Table1,2,True)+(B7-VLOOKUP(B7,Table1,1,True))*VLOOKUP(B7,Table1,3,True)
E.g. Y at starting point for trigger + (Movement beyond trigger) times Rate after trigger
All kinds of patterns can be concisely setup using that 3 column structure. It can also be translated into SQL database format for user configurable relationships.
Note: I have a template spreadsheet setup that automatically graphs VLOOKUP Range type data onto an XY type graph to help users confirm the values used and highlight potential issues. I cannot attach that here, but can forward a copy on request.
An early use of the three column VLOOKUP Range formula approach above, was also for Sales Incentives.
For that system, the base was not the raw monthly Sales amount, but monthly Sales divided by their Target, that was then mapped to the percentage of Bonus they would be paid.
This is the profile data used:
Trigger, Start, Incremental Rate
0, 0, 0
80, 0, 5 (minimum achievement is 80% after which get 5% more bonus for every 1% extra of target achieved)
100,100,1 (standard is 100% of bonus for achieving 100% of target, then 1% increment per 1%)
120,120,2 (to encourage overachievement, they offered 2% increase per 1% increase in target above 120%)
Visualisation of the pattern via a graph is very useful. Even though Sales Management was warned of the open ended final segment, that pattern was to be used. Then a salesman delayed 3 months worth of monthly sales until the last month of a quarter making them eligible for
600% of monthly bonus!
Alan … thanks for taking the time to share these wonderful applications of VLOOKUP! Appreciate it!
Thanks
Jeff
Such a simple change ‘False’ to ‘True’ yet so powerful and like Amy’s comments, such a time saver, many thanks for this little gem of information!
I recently came to this Vlookup page from one of your Blog emails. As a 20+ year user of Excel, I first I thought “OK, so here are some entry-level Excel techniques – maybe not much of interest…” but I have to say, your basic walk-through of how Vlookup functions with the “True” argument made me think about Vlookup in a way I hadn’t before. I mostly have used that function to “match” items between tables and to retrieve a specific data element. I had always considered the “True” option to be a handicap, since you could receive a potentially false “match” if your query value wasn’t in the searched table and it would force you to sort your data first. I really hadn’t thought about the utility of using the “True” Vlookup qualifier to select a value (eg. Bonus value) based on a “threshold” (i.e the first value that would meet the criteria – eg. a given sales level), given that Vlookup would trigger on that first value, but rather would likely have written a formula that looked for a value “between” a set of numbers. Often simple is better. Thanks for providing these examples.
By Ashwani Jaiswal
VLOOKUP to Return Multiple Values (Table of Contents)
VLOOKUP to Return Multiple Values
We all know that Vlookup in excel is used to lookup the exact or approximate match and we have all been doing this on our regular tasks. Vlookup looks up the value from the selected table range and returns the exact match as per the cell value it maps. But when we have multiple values in a table which are duplicate then we would only get the first value from the lookup range. The below the value which are duplicate will not reflect or lookup any value from the table range. But this is possible. We will be seeing, how to lookup multiple values in the below examples.
Excel functions, formula, charts, formatting creating excel dashboard & others
How to Use VLOOKUP to Return Multiple Values?
We cannot directly use lookup when we want to get multiple values in return. For this, we need to find the provision so that we would have unique values among all the values. We can create unique value by combining different cells or we can use different types of separators such as / (Slash), – (Hyphen), _ (underscore), or even spaces. Below we have column A with some names and all are duplicate.
To make these names unique value, we can add any number or special character so that all values will become unique. As we can see, in column B, after added numbers to each cell value, all the values have become unique.
Examples of VLOOKUP to Return Multiple Values
Lets us discuss the examples of VLOOKUP to Return Multiple Values.
Example #1
In this, we will be seeing, how to use Vlookup to get multiple values from one table to another. We have two tables below. Each table has same headers and also has the same Owner names and Product names in the same sequence. Now if we apply the vlookup in cell G2 to get the quantity sold for each Owner name then we will only get the first value of each owner name as the owner names are getting repeated.
To avoid such situations, we will insert a column in the first table and make a key using the Owner name and Product columns. We can use any type of separator here. In the Key column, we are using an underscore as a separator.
We will be able to see the final unique key column A as shown below.
Now in table 2, we will be applying Vlookup to get the value from Table 1 to Table 2. Insert vlookup function as shown below.
As per syntax, we need to select the lookup value in the same way we have created in table 1. For that, combine or concatenate F1 and G1 value with the help of underscore.
In the table array, select the complete table 1.
As we want to get Quantity Sold numbers from Table1 to Table2, so we will be selecting column 4 as Col Index.
Once we press enter and drag the formula till the end, all the values from the table1 will be fetched to table2’s Quantity Sold column.
Example #2
There is another way to apply Vlookup to get multiple values in return. For this, we will be using the same table which we have seen in the above example, but will better trim that table and work on its small part.
To lookup multiple values, here we will be using the Index function. The index function in excel is used to lookup the value in the form of a matrix. This means, Index lookups the value in the whole table with the help of chosen reference Column and Row index number.
As per the syntax of Index function, we need to select the array from where we want to get the value. Here our array is Column C.
Now as per syntax we will be using ROW and COLUMN numbers to get values. Here, we are using the Small function to get the smallest value first from the lookup array.
Same here, still finding the way to retrieve more than one value, Did you find the solution?
Thank you for your time!
Same here , still finding the way to retrive more than one value, Did you find the solution ?
Thank you for your time!
hello, what if in the columns, there are data aside from dates. How can I get the date from the range?
E.g. Column A – 03/19/2019, Column B – Approved, Column C – Pending, Column D – —. In this case, how can I get 03/19/2019?
I have two tables in a workbook.I need help to create a macro to run a report based on the dates as shown.
Name Due Date Amount Paid
JohnDoe3 23-Jan-18 $965.00 Yes
JohnDoe4 24-Jan-18 $145.00 Yes
JohnDoe5 25-Jan-18 $225.00 No
JohnDoe6 26-Jan-18 $336.00 No
JohnDoe7 27-Jan-18 $365.00 No
JohnDoe8 28-Jan-18 $124.00 No
JohnDoe9 29-Jan-18 $321.00 No
JohnDoe10 30-Jan-18 $333.00 No
JohnDoe11 31-Jan-18 $521.00 No
JohnDoe12 01-Feb-18 $333.00 Yes
JohnDoe13 02-Feb-18 $965.00 No
JohnDoe14 03-Feb-18 $145.00 No
JohnDoe15 04-Feb-18 $225.00 No
JohnDoe16 05-Feb-18 $336.00 No
JohnDoe17 06-Feb-18 $365.00 No
JohnDoe18 07-Feb-18 $124.00 No
JohnDoe19 08-Feb-18 $321.00 No
JohnDoe20 09-Feb-18 $333.00 No
JohnDoe1 10-Feb-18 $521.00 No
JohnDoe2 11-Feb-18 $333.00 No
FirstDate LastDate PayDay
19-Oct-17 01-Nov-17 16-Nov-17
02-Nov-17 15-Nov-17 30-Nov-17
16-Nov-17 29-Nov-17 14-Dec-17
30-Nov-17 13-Dec-17 28-Dec-17
14-Dec-17 27-Dec-17 11-Jan-18
28-Dec-17 10-Jan-18 25-Jan-18
11-Jan-18 24-Jan-18 08-Feb-18
25-Jan-18 07-Feb-18 22-Feb-18
08-Feb-18 21-Feb-18 08-Mar-18
Today is 05FEB18
If I run a report today (falls between 25-Jan-18 and 07-Feb-18 for pay period 22-Feb-18)
I want to pick up all the records for Due Dates 25JAn18 to 07FEB18 which has no in the Paid column and create a report.
Then update the paid column to yes.
the output should be as follows
JohnDoe5 25-Jan-18 $225.00
JohnDoe6 26-Jan-18 $336.00
JohnDoe7 27-Jan-18 $365.00
JohnDoe8 28-Jan-18 $124.00
JohnDoe9 29-Jan-18 $321.00
JohnDoe10 30-Jan-18 $333.00
JohnDoe11 31-Jan-18 $521.00
JohnDoe12 01-Feb-18 $333.00
JohnDoe13 02-Feb-18 $965.00
JohnDoe14 03-Feb-18 $145.00
JohnDoe15 04-Feb-18 $225.00
JohnDoe16 05-Feb-18 $336.00
JohnDoe17 06-Feb-18 $365.00
JohnDoe18 07-Feb-18 $124.00
- About Me
- Podcast
- Articles
- Blog
- Contact
Great day! I learned how to use vlookup in excel. Here was the problem. I had sent out a cold email to around 2500 people last week. The list of leads I used had a podcaster’s name, email, show id, and show name. Around 40% of people didn’t open the cold email, so I wanted to send them another one, just in case they missed it.
Sendy, the emailing platform, let me download a new list made up of the 40%. But the list only had the lead’s email, rather than his name, email, show id, and show name. I needed them all together to write a cold email people would actually open.
How then would I get a list of the 40% that had all 4 values?
The first thought I had was that I would “subtract” the cold email list from the original email list. I would try and find a formula which would copy a lead from the original list into a new list if it matched with the email from the list of unopened. So I googled around and tried to figure that out.
It didn’t work cause I was looking at the problem backwards.
Turns out that I needed to add to the unopened list from the original email list. I would add the name, show id, and show name to it. And that’s exactly what vlookup lets you do super quickly.
At its most basic, a vlookup does exactly what your eyes did when looking at the photo above. If you wanted to find how many calories a tall Caffe Latte had, first you’d look for “Caffe Latte” in the left hand column, then you’d look until you found the number under “Tall.” 225.
The actual formula in excel is =VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup]).
The lookup_value is basically what you want to look for in the left hand column first. the lookup_value from above was Caffe Latte.
table_array is the grid from which you’re trying to pull your value. For us, it was everything in between Flat White at the top left to 250 at the bottom right.
col_index_num is the column # which your value will be in. Since Tall was the 3rd column, col_index_num would be 3.
Lastly, there’s [range_lookup]. It’s supposed to be something about pulling approximate numbers vs exact, but in most cases, you’re going to want exact, which you denote by writing “FALSE”.
Thus, the vlookup formula we’d used to find the number of calories in a caffe latte is =VLOOKUP(Caffe Latte, Flat White:250, 3, FALSE).
The formula looks alchemical at first, but once you break it down, it gets friendly. In fact, I’m writing this blog because I know I’ll have forgotten how to use the formula.
Now you might be able to see how I used vlookup to fill in the missing values of my unopened list using the values in my original list. To make a long story short, I did it in 5 seconds.
The biggest lesson I learned from this was that the opportunity for just-in-time learning is something that emerges when you’re wrestling with a problem. First, to get a problem to grip you, you’ve got to spend a lot of time getting in the mood.
Then, you need to pay attention to how you formulate the problem. What exactly is it that you’re trying to accomplish, and what are the individual steps you think you need to take to do so?
Then, be playful. You can’t assume that the solution you have is the easiest, fastest, or most helpful way to do so. Try and see if you can prove yourself wrong, and imagine multiple ways of tackling the problem at different layers.
All of this is how you learn just in time. Deschooling yourself is fun.
