Microsoft Excel 2016 boasts a huge number of handy functions and utilities, many of which go untouched by the average user. If you find yourself repeatedly consulting the same table to find data, however, have a gander at VLOOKUP. Short for “vertical lookup,” VLOOKUP takes advantage of vertically-aligned tables to quickly locate data associated with a given value.
Note: though this tutorial was written for Microsoft Excel 2013, it works the same way in the latest version of Excel.
- Here’s how to freeze rows in Excel
- This is how to lock cells in Excel
- Check out our Excel tips roundup
If you know the name of a product, for instance, and you want to quickly determine its price, simply enter the product name into Excel and VLOOKUP will find the price for you. To the novice Excel user, setting up VLOOKUP can look like an intimidating process — but it needn’t be. Just follow our step-by-step tutorial on how to use VLOOKUP in Excel today.
How to use VLOOKUP in Excel
1. Click the cell where you want the VLOOKUP formula to be calculated.
2. Click Formulas at the top of the screen.
3. Click Lookup & Reference on the Ribbon.
4. Click VLOOKUP at the bottom of the drop-down menu.
5. Specify the cell in which you will enter the value whose data you’re looking for. In this case, our lookup value is H2, since this is where we will input the name of a tournament such as “PGA Championship,” so we input “H2” in the lookup_value box of the popup window. Once we’ve set up VLOOKUP properly, Excel will return the tournament’s Total Rating Value in cell H3 when we type the tournament name in cell H2.
6. Specify the data that you want VLOOKUP to use for its search in the table_array box. In this case, we’ve selected the entire table (excluding the headers).
7. Specify the column number which VLOOKUP will use to find the relevant data in the col_index_num box. Somewhat confusingly, VLOOKUP requires you to use the numerical value of the column rather than its letter value. In this case, we want VLOOKUP to use the Total Rating Value column — column D — so we enter the number 4.
8. Specify whether you need an exact match by entering either FALSE (exact match) or TRUE (approximate match) in the range_lookup box. In this case, we want an exact match so we enter FALSE.
9. Click OK at the bottom of the popup window.
10. Enter the value whose data you’re searching for. In our example, we want to find the Total Value Rating of the PGA Championship, so we type “PGA Championship” into cell H2 and VLOOKUP automatically produces the Total Value Rating (in this case, 914) in cell H3.
Using VLOOKUP, you can not only search for individual values, but also combine two worksheets into one. For example, if you have one worksheet with names and phone numbers and another sheet with names and email addresses, you can put the email addresses next to the names and phone numbers by using VLOOKUP.
More Microsoft Excel tips and tricks
There are a number of neat tips that’ll help you out when you’re managing your Excel spreadsheets. For example, when you protect a sheet or workbook, all of the cells will be locked, but you can also lock cells individually by right-clicking and selecting “Format Cells.” And if you need to, you can also freeze rows and columns by selecting “Freeze Panes” in the View tab.
But not everyone is a fan of Excel, so if you need to convert Excel spreadsheets to Google Sheets, we have a guide for that, as well as a guide on how to open Google Sheets in Excel.
For business users, we also have 10 Excel business tips that can help you keep your job, including guides on how to remove duplicate data, recover lost Excel files, use pivot tables to summarize data, and more.
There are times when our data is laid out in columns and rows. In these circumstances, we may need to look up to both the column and the row at the same time, such as in the data below.
But how can we achieve this?
VLOOKUP & MATCH
By combining the VLOOKUP function with the MATCH function, we can achieve a lookup to a row and a column at the same time; this is often referred to as a two-way lookup.
The MATCH function is a very useful; it returns the position of a lookup value within a range.
Using our example data; we can find the column number of “Jun” using the Match function.
The result of this formula is 6, as in the Range B1-M1 “Jun” is the 6th item. If we were to look up “Nov”, this would return 11, as that is the 11th item.
The last argument in the MATCH function is important. We will be using 0 as that will provide an exact match.
VLOOKUP & MATCH together
We can insert MATCH into the VLOOKUP function in place of the column number.
The VLOOKUP function counts the first column as 1, but our MATCH function starts at column B, so it is necessary to add 1 to the column number for the VLOOKUP to return the value from the correct column.
The formula in B12 is as follows:
Looking up multiple rows
We’ve seen, in previous posts, that it is possible to use VLOOKUP with multiple criteria where the data is in two or more columns. But what if we want to match multiple rows?
The example below shows July appearing twice in our data, once for 2016 and once for 2017. By making the MATCH formula an array formula we can match the two column criteria of month and year together.
The formula in cell B14 is:
This formula is starting to look a bit complicated now, so let’s break it down.
Firstly, this is an array formula. Type the formula into Excel without the < >, but press Ctrl+Alt+Enter to enter the formula. Excel will then add the < >by itself automatically.
Secondly, let’s just look at the first argument of the MATCH function. This is simply combining the values of “Jul” and “2016” together with a spacer character in the middle.
The next argument of the MATCH function creates a temporary array of values with a spacer character in the middle. This only works because it is an array formula.
The temporary array would include the following
The lookup value in the MATCH function is compared to this temporary array. Provided the year and the month match a value will be returned. By changing the year in cell B12 the value from N5, rather than B5 will be returned. The image below shows the result as 27, rather than 23.
Multiple condition rows and columns
If you ever need to match multiple condition rows and multiple condition columns together, then it’s probably best to consider the INDEX/MATCH/MATCH formula. As I’m not sure it’s possible to push the VLOOKUP formula that far.
Download the Advanced VLOOKUP Cheat Sheet
Download the Advanced VLOOKUP Cheat Sheet. It includes most of the tips and tricks we’ve covered in this series, including faster calculations, multiple criteria, left lookup and much more.
Please download it and pin it up at work, you can even forward it onto your friends and co-workers.
Download the file: Advanced VLOOKUP Cheat Sheet
Other posts in the Mastering VLOOKUP Series
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Excel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row.
But often there is a need to use the Excel VLOOKUP with multiple criteria.
How to Use VLOOKUP with Multiple Criteria
Suppose you have a data with students name, exam type, and the Math score (as shown below):
Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge.
One can argue that a better option would be to restructure the data set or use a Pivot Table. If that works for you, nothing like that. But in many cases, you are stuck with the data that you have and pivot table may not be an option.
In such cases, this tutorial is for you.
Now there are two ways you can get the lookup value using VLOOKUP with multiple criteria.
- Using a Helper Column.
- Using the CHOOSE function.
VLOOKUP with Multiple Criteria – Using a Helper Column
I am a fan of helper columns in Excel.
I find two significant advantages of using helper columns over array formulas:
- It makes it easy to understand what’s going on in the worksheet.
- It makes it faster as compared with the array functions (noticeable in large data sets).
Now, don’t get me wrong. I am not against array formulas. I love the amazing things can be done with array formulas. It’s just that I save them for special occasions when all other options are of no help.
Coming back to the question in point, the helper column is needed to create a unique qualifier. This unique qualifier can then be used to lookup the correct value. For example, there are three Matt in the data, but there is only one combination of Matt and Unit Test or Matt and Mid-Term.
Here are the steps:
- Insert a Helper Column between column B and C.
- Use the following formula in the helper column: =A2&”|”&B2
- This would create unique qualifiers for each instance as shown below.
How does this work?
We create unique qualifiers for each instance of a name and the exam. In the VLOOKUP function used here, the lookup value was modified to $F3&”|”&G$2 so that both the lookup criteria are combined and are used as a single lookup value. For example, the lookup value for the VLOOKUP function in G2 is Matt|Unit Test . Now this lookup value is used to get the score from C2:D19.
There are a couple of questions that are likely to come to your mind, so I thought I will try and answer it here:
- Why have I used | symbol while joining the two criteria? – In some exceptionally rare (but possible) conditions, you may have two criteria that are different but ends up giving the same result when combined. Here is a very simple example (forgive me for my lack of creativity here):
Note that while A2 and A3 are different and B2 and B3 are different, the combinations end up being the same. But if you use a separator, then even the combination would be different (D2 and D3).
- Why did I insert the helper column between column B and C and not in the extreme left? – There is no harm in inserting the helper column to the extreme left. In fact, if you don’t want to temper with the original data, that should be the way to go. I did it as it makes me use less number of cells in the VLOOKUP function. Instead of having 4 columns in the table array, I could manage with only 2 columns. But that’s just me.
Now there is no one size that fits all. Some people may prefer to not use any helper column while using VLOOKUP with multiple criteria.
So here is the non-helper column method for you.
Download the Example File
VLOOKUP with Multiple Criteria – Using the CHOOSE Function
Using array formulas instead of helper columns saves you worksheet real estate, and the performance can be equally good if used less number of times in a workbook.
Considering the same data set as used above, here is the formula that will give you the result:
Since this is an array formula, use it with Control + Shift + Enter, instead of just Enter.
How does this work?
The formula also uses the concept of a helper column. The difference is that instead of putting the helper column in the worksheet, consider it as virtual helper data that is a part of the formula.
Let me show you what I mean by virtual helper data.
In the above illustration, as I select the CHOOSE part of the formula and press F9, it shows the result that the CHOOSE formula would give.
It’s an array where a comma represents next cell in the same row and semicolon represents that the following data is in the next column. Hence, this formula creates 2 columns of data – One column has the unique identifier and one has the score.
Now, when you use the VLOOKUP function, it simply looks for the value in the first column (of this virtual 2 column data) and returns the corresponding score.
Download the Example File
INDEX/MATCH or SUMPRODUCT).
Is there any other way you know to do this? If yes, do share with me in the comments section.
In this video, we’ll look at how to use VLOOKUP to lookup values in an Excel Table.
On this worksheet, I have a table that contains employee data, named Table1.
To illustrate how to work with VLOOKUP when the source data is in a table, I’ll set up formulas to the right to extract data from the table, matching on an employee ID.
First, I’ll select the table header and use paste special with transpose to get the field values.
Then I’ll add some formatting, and an ID value so I have something to match against.
Now I’ll write the first VLOOKUP formula.
For the lookup, I want the value from K4, locked so it doesn’t change when I copy the formula down.
For table array, I want the lookup table itself, Table1.
Now, because VLOOKUP only looks to the right, it’s important that the lookup is to the left of values we we want to retrieve.
In this table, the ID is the leftmost column, so we can get any value.
For column ID, I need 2, since the first name is the second column in the table.
Match type is zero or false, since I want to force an exact match.
When I enter the formula, we get “Julie”, which is correct.
If I copy the formula down to the next row, I only need to adjust the column number to get last name.
And, I can do the same for all the other fields.
You might wonder if there’s an easy way to avoid hardcoding the column number in the formula?
The answer is yes. Since the names in column J match values in the table header, I can use the MATCH function get an index for each field.
To demonstrate, I’ll use MATCH in column L by itself.
The lookup value comes from column J.
The array is the table header.
Match type is zero, for exact match.
When I copy the formula down, I get a numeric index for each field.
Now I just need to copy MATCH formula into VLOOKUP to replace the hardcoded column index.
This is an example of nesting functions inside a formula.
When I copy the formula down, I get a result for each field.
I’ll go ahead and remove the helper column.
When I change the ID value, everything works.
And, because I’m using a table, I can easily add more data, and the same formulas will continue to work without edits.
Finally, using match this way provides a really nice benefit: I can easily reorder fields in the output formulas, or, in the table itself, and the VLOOKUP formulas keep working.
How to use vlookup exact and approximate match in Excel?
In Excel, vlookup is one of the most important functions for us to search a value in the left-most column of the table and return the value in the same row of the range. But, do you apply the vlookup function successfully in Excel? This article, I will talk about how to use the vlookup function in Excel.
Use vlookup function to get the exact matches in Excel
First, you must know the vlookup syntax and details of the parameters.
The syntax of VLOOKUP function in Excel is:
- lookup_value: the value to search based on in the first column of the table;
- table_array: the range of cells that contains the data, you can use a reference to a range such as A1:D10, or a range name;
- col_index_num: the column number in the table_array argument from which the matching value must be returned;
- range_lookup: is optional operation, FALSE to find an exact match, and TRUE to find the approximate match.
Now, I will take an example to explain this vlookup function to get the exact matches, supposing you have the following list of persons’ information in range A2:D12:
In this case, you want to find the corresponding names with the IDs in the same row, please use the following vlookup formula into a blank cell where you want to get the result:
Then, drag the fill handle down to the cells to apply this formula, and you will return the results as below screenshot shown:
1. In the above formula: F2 is the value which you want to return its relative information, A2:D12 is the data range you use, the number 2 indicates the column number that your matched value is returned and the FALSE refers to the exact match.
2. If your criteria value is not found in the data range, you will get an error value #N/A.
If you are interested in VLOOKUP function in Excel, Kutools for Excel‘s Super LOOKUP supports some powerful Vlookup formulas for you, you can quickly do the Vlookup function 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!
Use vlookup function to get the approximate matches in Excel
Sometimes, your specified data is not in the data range, to get the nearest match with the given data, you need to use the vlookup to get an approximate match.
If you have the following range data, the specified quantity number 58 is not in the Quantity column, how to get its closest unit price in column B?
Please enter the below formula into a blank cell:
Then, drag the fill handle down to the cells to apply this formula, and you will get the approximate matches based on the given values, see screenshot:
1. In the above formula: D2 is the value which you want to return its relative information, A2:B10 is the data range you use, the number 2 indicates the column number that your matched value is returned and the TRUE refers to the approximate match.
2. The approximate match returns the next largest value that is less than your specific lookup value.
3. To use the vlookup function to get an approximate match value, your first column in the table must be sorted in ascending order, otherwise it will return a wrong result.
Vlookup to get the exact matches with a handy feature
If you have Kutools for Excel, with its Look for a value in list formula, you can quickly return the matching data based on the specific values.
Note: To apply this Look for a value in list, 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 a cell where you want to put the matched result.
2. Then click Kutools > Formula Helper > Formula Helper, see screenshot:
3. In the Formulas Helper dialog box, please do the following operations:
(1.) In the Formula Type drop down list, please select Lookup option;
(2.) Then, select Look for a value in list option in the Choose a formula list box;
(3.) And then, in the Arguments input text boxes, please do the following operations:
- Click first button beside Table_array to select the data range which you want to use;
- Click second button beside Lookup_value to select the criteria that you want to return its matched value;
- Click third button beside Column to select the column contains the value you want to return.
4. Then click Ok, and the first matched data based on a specific value has been returned at once. You just need to drag the fill handle to apply this formula to other cells you need,see screenshot:
Note: If the value is not found in the data range, you will get an error value #N/A.
If you want to save yourself a huge amount of time when reconciling data, learn how to use VLOOKUP to compare two lists or columns of data.
Years ago the Training and Development manager of a large organisation invited me to spend some time in their Accounts department in order to observe how the Accounts team dealt with their day-to-day tasks. My job was to report on any process that could be improved using Microsoft® Excel.
I’ll always remember asking one of the team (a “just-out-of-school” new recruit) what he was working on after I’d observed him sitting with a red biro and a ruler, diligently drawing red lines on 2 separate sheets of paper. It turned out he was trying to reconcile a customer’s account.
The customer had sent him a list of the invoices and amounts they had on record as being paid.
The young administrator had printed a list of all of the customer’s invoices and payments that his company had on file. He then proceeded to use his red biro to place a line through any invoice where the details matched on both sheets. He explained to me that once he completed this job he would then have eliminated the payments where both sheets matched and he would then only deal with the invoices that didn’t match.
This way of reconciling a customer’s account was taking hours, and in some cases days to complete.
I couldn’t wait to show this young guy how to use the VLOOKUP function. The steps below cover how I explained to him (and others in his department) the best way to reconcile the data using a VLOOKUP.
In this article, we will learn how to use Excel ISNA function with VLOOKUP to ignore the #N/A error while calculating the formula in MS Excel 2016.
Excel VLOOKUP function is used to look up and fetch data from a specific column of a table. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right.
How to use the Vlookup function in excel?
VLOOKUP function in Excel is used to look up and retrieve data from a specific column of a table.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
How to use the ISNA function in excel?
ISNA function used for checking the #N/A error while calculation by using any function.
How to use the if function in excel?
IF function works on logic_test and returns values based on the result of the test.
=IF(Logic_test, [Value_if_True], [Value_if_False])
Let’s get this with an example.
Here we have two sheets, we have to get the BIRTHDT from SHEET 1 table to SHEET 2 Table using the functions.
Write the formula in C2 cell of SHEET 2.
Vlookup function in excel looks for the 6th column of the SHEET 1 matching REC_ID in SHEET 1 from SHEET 2.
ISNA function in excel looks for the #NA error and passes it on to IF function.
IF formula in excel checks if any #NA error occurs, it print “NotFound” instead of the #NA error.
Copy the formula in other cells, select the cells including the first cell where the formula is already applied, use shortcut key Ctrl + D.
As you can see in the above image, the formula is working fine. And the user can work without getting irritated by #NA error.
Hope you understood IF, ISNA and VLOOKUP function in Excel 2016, 2013 and 2010. There are more articles on VLOOKUP Examples and HLOOKUP Examples here. Please do check more links here and if you have any unresolved query, please state that in the comment box below. We will help you.
Below are the examples to learn more:-
Two-way table lookups – can’t get the function to work
Question asked by user:-
If anybody can help with this I’m trying to get the correct price from the table from the data entered in AB2 and AB3. The function used is in cell AB7.
IF Statement combining multiple ‘IF TRUE’ conditions
Question asked by user:-
Assuming that I have a worksheet with columns A,B,C and D on it, now I want an IF statement in column E which follows this argument:
IF A1 is “0” or “N/A”, then show N/A, but if A1 is any other number, then B*(C+D)
I hope that’s easy to follow. I have tried so hard to get this to work but just can’t.
As ever, any help would be hugely appreciated.
Many times, we need to write abbreviations of names, places, and things. This can be done easily by using inbuilt functions of Excel. In order to determine two-letter abbreviations from full state names (i.e., Los Angeles to LA), you can use a simple formula using VLOOKUP.
VLOOKUP function to abbreviate state names in Excel
VLOOKUP is an Excel function that is used to look up and retrieve data from a specific lookup column in a table. It is also categorized as a lookup/reference function.
Syntax of VLOOKUP
=VLOOKUP(value, table,index number,[range_lookup])
In this case, there is no direct way for you to finish this job in Excel. But, you can list all the state names and their relative abbreviations first in a table and then apply the VLOOKUP function to get any of the required abbreviations.
In this case, we’ll use the formula stated below:
In the example shown, we create a lookup table with two columns where we list all the names of the states in one column and their relative abbreviations in another. Then, we create another table consisting of three columns where we mention state name in one column, their population in second and in the third column, we’ll get their abbreviation displayed.
We apply the formula in E5
Here, ‘states’ is a named range/lookup table G5:H11
Figure 1: Illustration of the VLOOKUP formula
Once we apply this formula, we’ll get the abbreviation NW for New York in E5. Similarly, on dragging the formula to other cells, we’ll get the abbreviation of other states as shown.
Figure 2: VLOOKUP for abbreviating state name
VLOOKUP will locate the matching entry in the “states” table and in return, it will give the corresponding two-letter abbreviations in column E.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Users of UW-Madison’s institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.
How does the vLookup formula work?
Excel's vLookup formula pulls data from one spreadsheet into another by matching on a unique identifier located in both spreadsheets. For example, we want to add a column for email address but that data exists on a separate spreadsheet. vLookup can pull email addresses from Spreadsheet 2 into Spreadsheet 1 by matching CampusID 555123123 in both spreadsheets.
How to Use the vLookup Wizard
Locate where you want the data to go. Click that cell only once.
At the top, go to the Formulas taband click Lookup & Reference
Excel's vLookup wizard will pop up. We'll walk through each part of the formula.
Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student [email protected] is the email in one row and Student [email protected] in the other, Excel will choose one of the emails for you.
Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:
Finally, copy and paste the formula to pull emails for the rest of the column.
(Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
If you feel comfortable with the vLookup tool instructions above, you can type the formula directly in the cell instead of using the wizard.
Type the beginning of the formula: =VLOOKUP(
The formula guide will appear below.
(Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
Follow the guide and enter each value. Remember to insert a comma between each value.
Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)
Advanced Tip on Relative References
The position of the lookup value (Unique ID) in relation to the vLookup formula is maintained when you copy and paste. If you paste the formula one cell down (to E3), it looks up the Unique ID that is also one cell down (B3). The same is true when copying right, left or up.
In other words, the formula will stay x number of columns and y number of rows away from the lookup value – no matter where you paste the formula. In our example, the formula is the fourth column from the CampusID and in the same row. No matter where you paste the formula (in this example), it will always look up the cell that is the fourth cell to the left in the same row.
However, it is possible to lock cells in place by inserting 1 or more $ signs. This means, no matter where you paste the formula, it will always reference the same cell.When copying and pasting the formula, use the $ sign to lock in cells.
To lock in the lookup value in cell B1, insert $ signs before the column and the row:
VLOOKUP in Excel is a very useful function used for lookup and reference. It looks for the desired values from one row to another to find a match. Using a combination INDEX and MATCH , we can perform the same operations as VLOOKUP. INDEX returns the value of a cell in a table based on the column and row number. MATCH returns the position of a cell in a row or column. Combining these two functions we can look up a value both horizontally and vertically. In this tutorial, we will look the differences between VLOOKUP and INDEX MATCH and when and how to use them.
VLOOKUP uses the syntax : =VLOOKUP(value, table_array, col_index,[range_lookup]) .
Why use INDEX MATCH instead of VLOOKUP?
Using INDEX MATCH instead of VLOOKUP is preferred by many Excel professionals. VLOOKUP has many limitations. You can overcome these by using INDEX MATCH. You may use VLOOKUP when the data is relatively small and the columns will not be inserted/deleted. But in other cases, it is best to use a combination of INDEX and MATCH functions. You use the following syntax using INDEX and MATCH together: =INDEX(range, MATCH(lookup_value, lookup_range, match_type)) . The main advantages of using INDEX MATCH than VLOOKUP are:
Dynamic Column Reference
The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from. As you are using a static reference, adding a new column to the table breaks the VLOOKUP formula. INDEX MATCH allows you to click to choose which column you want to pull the value from. This leads to fewer errors.
To find the price from the Product ID, select the cell G2 and assign the formula =VLOOKUP(F2,$A$2:$D$9,3,FALSE) . Click OK to apply the formula to cell G2 which would show the price for the product in cell F2.
To get the same result using INDEX MATCH, you need to apply the formula =INDEX($C$2:$C$9,MATCH(F2,$A$2:$A$9,0)) to cell G2.
2. Insert/Delete Columns Safely
VLOOKUP uses a static column reference. This breaks the formula each time you add/delete a new column. You can manually set the formula to refer the correct column. But it is a lot of work especially when you have a large data set. INDEX MATCH solves this problem by using a dynamic column reference. You can add/delete columns without distorting the array.
If you add a new column in the previous example named Quantity Required after the Names , the previous formula =VLOOKUP(F2,$A$2:$D$9,3,FALSE) for price would return the value for the Quantity Required which is incorrect.
Using INDEX MATCH will always return the price even after adding/deleting rows as you are using a dynamic reference. To use INDEX MATCH you will assign the formula =INDEX($D$2:$D$9,MATCH(G2,$A$2:$A$9,0)) to cell H2.
3. Lookup Value Size Limit
You need to make sure the total length of your lookup criteria should not exceed 255 characters, otherwise you will end up having the #VALUE! error. But INDEX MATCH can lookup values more than 255 characters in length.
If you had a new column QR Code containing the 320 characters QR Codes for the products from the previous example, the formula =VLOOKUP(F2,$A$2:$D$5,4,FALSE) to find the quantity available would result in a #VALUE error.
The formula does not work here as the lookup value in cell F2 exceeds 255 symbols. Instead, you need to use the INDEX / MATCH function =INDEX($D$2:$D$5,MATCH(F2,$B$2:$B$5,1)).
4. Higher processing speed
The difference in performances between VLOOKUP and INDEX/MATCH will be negligible if your table_array is small. But if your worksheets contain a lot of rows and formulas, INDEX MATCH will work much faster than VLOOKUP because Excel will have to process only the lookup and return columns rather than the entire table array.
5. Lookup Value Position
VLOOKUP will only work if the lookup value is in the first column. VLOOKUP cannot look to its left. However, INDEX MATCH solves this problem as it performs the lookup both horizontally and vertically. So, it doesn’t require the lookup value to be in the first column, it can be anywhere.
In this example, VLOOKUP fails to return the Dept. looking for name as it is not in the first column. Using INDEX MATCH you can solve this problem. Assign the formula =INDEX(C2:C8,MATCH(E2,B2:B8,0)) in cell F2 and it will return the Dept. Logistics for the employee Gene Wagner .
VLOOKUP assumes by default that the first column in the table array is sorted alphabetically. If your table is not sorted that way, VLOOKUP will return the first closest match which in many cases may not be your desired output. However, INDEX MATCH will return the exact match if it is specified in the formula even if the column for lookup is not sorted.
VLOOKUP is a very effective lookup and reference function. It has some limitations which can be overcome by using INDEX MATCH functions. When you have a small data set and do not have the issues mentioned in this article, you may use VLOOKUP. Otherwise, like most Excel experts, it is best to use INDEX MATCH.
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.
When you need to sum values with a certain condition, how do you handle it when the criteria are in different tables? Combining Excel’s SUMIF and VLOOKUP functions is one option. In this article, we’ll show you how to do that, including other examples of using both functions together in one formula!
How to do SUMIF and VLOOKUP together in Excel
You can use VLOOKUP and SUMIF (or SUMIFS for multiple criteria) together in Excel for various purposes—for example:
- VLOOKUP within SUMIF, when you need to sum values based on conditions, but you also have to lookup from another table to get the correct criteria value.
- SUMIF within VLOOKUP, when you need to search for a value based on the total you’ve summed. Another case is when you need to do a lookup by criteria but want a non-numeric value instead of numbers as a result.
- SUMIF + VLOOKUP + another function, for more complex scenarios. For example, you can use SUMIF + VLOOKUP + SUMPRODUCT when you want to sum across multiple sheets then find the approximate match from a lookup table based on the totals you’ve got.
SUMIF and VLOOKUP are both very useful formulas. You can use them in a variety of scenarios (including those not mentioned above). Understanding how each of these functions works is crucial to being able to use them properly when you need them!
If you haven’t used either SUMIF or VLOOKUP, please check out our articles covering the basics of these functions: Excel SUMIF function and Excel VLOOKUP function.
Excel SUMIF + VLOOKUP examples
Now, let’s explore some examples below on how to use Excel SUMIF & VLOOKUP together.
#1: Excel SUMIF with VLOOKUP for looking up the criteria value
Suppose you have the following spreadsheet that contains Orders and Products data in two separate tables. Then, you want to add up the amount for Firecracker and put the result in I3.
But, as you can see, the Orders table does not have a column for product names. This means that you can’t just sum up the amount for your orders where the product name is equal to Firecracker directly with the SUMIF function.
The solution? You need to get the product number of Firecracker first, then use it as the criteria in your SUMIF function. Here are the steps:
Step 1: Write the VLOOKUP formula in I3 to get the product number of Firecracker.
The formula looks for a value that exactly matches “Firecracker” in the first column of the range E3:F10. Then, it returns “SF706” from the second column of the range (column F).
Step 2: Use the VLOOKUP in a SUMIF, as shown below:
The SUMIF formula adds the amount in C3:C14 where any value in B3:B14 equals “SF706“. You can see the final result in I3, which is $400.
#2: Excel VLOOKUP with SUMIFS to lookup with multiple criteria
For this example, we use a small subset of an Employee dataset stored in Airtable. We exported data from Airtable to Excel because, as you know, Microsoft Excel is one of the most convenient tools for data analysis. We manage the process automatically using Coupler.io, an integration tool for exporting data from different sources to Excel. With Coupler.io, anyone can set up seamless Microsoft Excel integrations with other apps, such as Airtable, Jira, Shopify, Slack, and many more!
Now, with the following Employee data, suppose you want to find an employee’s email address based on their first name, last name, and department.
Assume that each Employee ID value is unique, and each department does not contain any employees with the same full name.
It would be easy if you only need to lookup based on the Employee ID. You could just use VLOOKUP, and it’s done. But unfortunately, you can’t use VLOOKUP because you need to do a lookup on multiple columns.
So, what’s the solution? Well, luckily, you can combine both Excel VLOOKUP and SUMIFS to get the result you want! Use SUMIFS to get the ID of the specified employee based on their first name, last name, and department. Then, use that ID in a VLOOKUP function to return the email address.
Here are the steps:
Step 1: Use SUMIFS to get the ID of the specified employee:
As you can see, the above formula in H4 returns the ID of Janie Jones from the Engineering department, which is 113004.
Step 2: Use the SUMIFS within a VLOOKUP to find an email address based on the employee ID, as shown below:
The VLOOKUP formula above uses the result of the SUMIFS function as the lookup value. It then finds the exact match in range A2:E10 and returns “[email protected]“, which is located in the fifth column of the range.
#3: Excel SUMIF + SUMPRODUCT + VLOOKUP to sum values across multiple sheets
Suppose you have the following spreadsheet with four worksheets: Summary, Apr, May, and Jun. In the Summary worksheet, you want to calculate the quarterly bonus for each salesperson using the lookup table on the right.
In this scenario, you will sum up the sales across Apr, May, and Jun for each salesperson. With these numbers, you will be able to determine the bonuses based on the idea of breakpoints, as follows:
Step 2: For the first salesperson, write a SUMIF Formula for one sheet only – for example, Apr.
Step 3: Wrap the SUMIF inside SUMPRODUCT.
We need to do this because, in the next step (Step 4), we’re going to use SUMIF to sum across multiple sheets using the sheets’ reference in G2:G5. The SUMIF will return an array, so we use SUMPRODUCT to make sure that everything will get summed up correctly.
Step 4: Sum across the sheets by using the list of sheet names as a reference. Let’s use an absolute range — so, in this case,
- Replace Apr!A2:A10 with INDIRECT(“‘”&$G$3:$G$5&”‘!”&”A2:A10”)
- Replace Apr!C2:C10 with INDIRECT(“‘”&$G$3:$G$5&”‘!”&”C2:C10”)
The complete formula will be:
From the result in B2, you can see that Lisa Brown’s total sales for the months of April, May, and June were $38,000 in total. Her bonus will be calculated based on this amount.
Step 5: Calculate the bonus by finding the approximate matches in the lookup table. To do this, wrap the SUMPRODUCT inside VLOOKUP:
Step 6: Drag the formula down to B5 to apply the formula for other salespeople.
Congratulations! You’ve calculated the bonus for each salesperson across multiple sheets by using three functions: SUMIF, SUMPRODUCT, and VLOOKUP!
We’ve shown you how to use Excel SUMIF and VLOOKUP together in one formula. And we hope you found the examples helpful for figuring out when they complement each other best.
Check out also another time saving combination of SUM and VLOOKUP functions.
In this article, we will learn about how to use VLOOKUP function in Excel to find the data by Date.
VLOOKUP just searches for the row of a given value in the first column of a table and returns asked column’s value in that row.
Lookup_date: The date by which you want to search in the first column of Table Array.
Table_array: The Table in which you want to look up/search
col_index_number: The column number in Table Array from which you want to fetch results.
[range_lookup]: FALSE if you want to search for exact value, TRUE if you want an approximate Date match.
Here we know LOOKUP value is by Date
Let’s understand this function using it an example.
Here we have a Data Table and Vlookup Information and we need to find the Total Price by the date 5/7/2019.
To find the value, use the Vlookup formula
The Formula looks up for the date in K3 cell in the table A1:H15.
Now once the date is found by the formula, It will lookup 8th column of the Table_array.
As the Last argument is False it will look for exact Date Match.
As you can see here, the formula has the Total price for the provided date.