Get Ahead with Excel: An Intermediate Guide with VLOOKUP Examples, Formulas, Syntax, and Practice Samples

In Excel, VLOOKUP is a lookup/reference function that helps you find an item in a table or range of cells vertically by their row. Four arguments comprise the syntax of the VLOOKUP function; the arguments are the value you want to use as a reference, the range or table of cells that hold the value you seek, the column number for your return value, and whether you want an exact or approximate match. Respectively, these arguments in the VLOOKUP formula are lookup_value, table_array, col_index_num, and [range_lookup].

There are two ways to input formulas in Excel: You can type the formula directly in the cell or use Excel’s Formula Wizard.

Follow these steps to use Excel’s Formula Wizard for a VLOOKUP function.

1. Click theFormulastab.
2. Click theInsert Functionicon on the top-left corner of the ribbon.
   
   
   
   
   
3. When theInsert Functionbox appears, typevlookupinto the search box, and clickGo. ClickOK.

TheFunctions Argumentbox will then appear, which walks you through inputting the VLOOKUP formula.

How VLOOKUP Works

为了理解VLOOKUP是如何工作的,让我们用你的足总vorite coffee order. The menu for your coffee shop is a good example of how you often perform a version of VLOOKUP in your head, scanning across from your drink of choice to the price under the preferred size of your drink.

In Excel, VLOOKUP scans the cell in the data table and looks for the data and the location you specify. When you hone in on the Hot menu, you see that there are three columns (sizes): 12 oz., 16 oz., and 20 oz. If you were ordering a 16-oz. chai latte, you would scan down the column of drinks until you find the chai latte, then across the row until you stop at the price in the appropriate (16 oz.) column. From there, you would determine that the drink costs $3.39. This process is not much different from what happens in Excel using the VLOOKUP function.

We’ll use the same coffee example in Excel. Download theExcel Sample Datafile to follow along. A note about the sample file: All the data you need for the majority of the examples explained throughout this tutorial are included in the file. Simply scroll through the tabs to find the corresponding example. Some worksheets already have the VLOOKUP formulas completed in the cells noted in the tutorial. To do it yourself, delete the existing formula and enter the one in this tutorial.

在Excel工作表,您可以扫描column to find chai latte, then across to the column for 16 oz. However, because the majority of Excel spreadsheets will not be a simple coffee order, Excel provides functions to help you look up your data. In this example, you could use VLOOKUP to determine the cost of your 16-oz. chai latte. Follow these steps to perform an exact match VLOOKUP in Excel.

1. Click theCoffee Exampleworksheet tab in your sample workbook.
   
   
2. On the worksheet, you see the same Hot menu from the coffee shop blackboard image above. Use the VLOOKUP function, and click cellB19, where you will enter your formula.
3. Type=VLOOKUP(to let Excel know that you are creating a VLOOKUP formula) in the formula bar. Excel will open the tool tip box to help you keep track of the components of the formula. As you enter your formula, Excel bolds the portion you are inputting in the tool tip box to help you track the calculation.
   
   
4. Type the first argument, lookup_value, as cellA19,你想要查找的值。通过温馨的ing this value outside of your data table (menu), you can change the data and still have the correct results when you look up a value. Type a comma (,) between each argument.
   
   
5. Type the second argument, table_array. This is the entirety of your data table. You can manually typeA1:D14, or you can use your mouse to highlight the cells on the table and Excel populates its parameters into the formula. Type a comma(,).
   
   
6. The next argument is col_index_col. This is the column number in the data table that you are scanning to get to the row hosting the piece of data you defined in the first argument. In other words, in the first argument, you told Excel what data piece it was looking for: in this case, “Chai latte”. This defines where Excel should start. In the second argument, you told Excel the parameters of the data table. Now that you’ve defined the data table parameters, you are telling Excel which column to scan for your answer. In this case, enter 3. This is the third column from the lookup values. The lookup values must always go in the first column. Then type a comma(,).
7. The last VLOOKUP argument for an exact lookup is [range_lookup]. TypeFALSEor0. Your lookup will now be exact.
   
   
8. Type a close parenthesis ) and clickEnteron the keyboard.
   
   
   
   
   Uh-oh! As you can see from the example, you receive an #N/A error. This means the data you seek is either not where you told Excel to look or it is in the wrong format. In this case, Excel does not recognize the data present as the data it seeks because there is additional data in the cell: the choice of mate, masala, or rooibos.
   
   
   
   
   To account for other data in the cell, Excel has extra characters that help out. For example, if your data table is loaded from an accounting program or other software, it may add extra spaces to each cell. Excel recognizes these extra spaces as characters and could produce the #N/A error.
9. Type an asterisk (*) after your drink order. The asterisk tells Excel to disregard any characters after “Chai latte”. Now, Excel returns the correct price.

This example was an exact match VLOOKUP and returned the exact value you were seeking — not an approximation.

The fourth argument in the VLOOKUP formula, [range_lookup], is a Boolean argument, meaning that you can give it a TRUE/FALSE value or representation of a TRUE/FALSE value such as 1/0. In this formula, FALSE is an exact match, and TRUE is an approximate match.

The argument name, [range_lookup], may be misleading to many users. The approximate match does not work well with text strings and is mostly meant for strings of numbers, especially when you are looking for a value from within a range. For this type of example, you would perform an approximate match VLOOKUP.

TheBonus Exampleworksheet in theExcel Sample Datafile is an example of how to calculate sales bonuses using VLOOKUP. We added the commission table to the worksheet as a reference to show what commission percent each salesperson has earned for the quarter. Bonuses in this company start after an employee reaches $40,000 in sales and bump up percentage points as they reach each threshold. Note the sales column in the reference table is arranged in ascending order, which is important for a lookup of this type.

Follow these steps to perform an approximate match VLOOKUP in Excel.

1. Click theBonus Exampleworksheet tab.

2. Click cellC2, and type=VLOOKUP(in the formula bar. This is your first VLOOKUP formula. The tool tip bar appears when you type this formula introduction.

3. TypeB2(the value that Excel will use as a reference in the table, called the lookup_value) for the first argument in your VLOOKUP. It is highlighted in the same color as the argument it represents in your formula. Between each argument, type a comma (,).

4. The second argument is table_array, which is the data table Excel uses to look up your values. You can either highlight this table manually by clicking the first cell and holding down your mouse until you have a complete selection, or you can type the rangeF4:G10. Type a comma (,).

5. The third argument is col_index_num, which tells Excel the column in your data table to find the return data. Type 2 for this value. Type a comma (,).

At this point, you can type a close parenthesis ) and clickEnteron the keyboard or complete the formula with a fourth argument. The fourth argument automatically defaults to the approximate match, but if you want the practice, go to the next step.

6. The fourth and final argument in the approximate match VLOOKUP is [range_lookup]. If you choose to enter this argument, typeTRUEor1.

7. Type the close parenthesis ) and clickEnteron the keyboard. The complete formula is=VLOOKUP(B2,F4:G10,2,1).

As you can see,Mike Hayesearned a 6 percent commission this quarter because he is over the threshold of $150,000 but not yet at $200,000 in sales.

8. To get the formulas into the rest of the sales team’s row, click cellC2, right-click on the mouse, and clickCopy. Right-click on the mouse, clickPaste Special, clickFormulas(or thePaste Formulaicon), and highlight cellsC3throughC10by selecting the rangeC3:C10. ClickEnteron your keyboard.

This tiles the formula down the Commission % column.

Uh-oh! Some of your formulas have an #N/A error. This occurs because tiling your formula down the Commission % column also tiles the formula down your commission data table, and Excel is looking at blank cells for your table range. To fix this, add absolute references ($) to your formula.

9. In your first formula in cellC2, add$before each column and row identifier in your data table range F4:G10, so it reads$F$4:$G$10. An easy and quick way to do this is to highlightF4:G10and clickF4on the keyboard. ClickEnteron the keyboard.

10. Repeat step 8, copyingC2and pasting the rest of the column.

Uh-oh! You still have an #N/A error. However, this error is due to Sylvie King not meeting the lowest threshold eligible for a sales commission in the data table, so Excel did not find a value for $23,000 in the data table. To fix this error, you have the choice to add a lower threshold to your data table — or hope that Sylvie meets $40,000 by next quarter!

VLOOKUP uses data in a defined data table range to look vertically down a column. It specifies to Excel that the data is in a column form. A similar function is HLOOKUP, which performs a lookup in a defined data table range with the identical syntax, but it tells Excel that your data is set up in rows, or horizontally.

Many people learn the basics of VLOOKUP and HLOOKUP early in their Excel education. You may also combine the two functions when your data table is in a matrix format. In other words, your lookup values should be on the top and left side of your table. This is a two-dimensional lookup, using two pieces of information.

Excel data tables help you organize your data. After you build tables in Excel, they automatically resize and incorporate additional data in your formulas and keep your formulas consistent as they automatically populate. Named data tables give you an edge while building and locating formulas in large workbooks.

Follow these steps to build a structured reference table in Excel.

1. Click on theStructured Referenceworksheet tab in theExcel Sample Datafile.
   
   
2. Click theInserttab, and click on any cell outside of the data table. ClickTablesandTable,或者使用Ctrl + T键盘上的快捷键。没有te: This information is already filled out in the sample data file; to create a new table follow these steps in a blank sheet.

TheCreate Tabledialogue box appears.

If your data table has headers, ensure that you click theMy table has headerscheck box. Review the parameters of the table to ensure that all your data is included. Click OK. The data table autoformats with the alternate fill color.

To add a table name, click theDesigntab (in some versions of Excel, this will appear in theTabletab). Under Table Name, type the nameStructuredReference. The name must start with either a letter or underscore (_), must not have a space or special characters, and must not conflict with an existing name in the workbook.

Once you name the table, you can add more headers, which are automatically formatted to match the rest of the table.

You can also create a lookup table, in a process similar to creating any table in Excel. Follow these steps to create a VLOOKUP table.

1. Click on theCreate Lookup Tableworksheet tab in theExcel Sample Datafile.
   
   
2. Enter headings in the first row. This example already has two headers,NameandSales QTD, and data entered into the table.
3. 列,第一列,而且应该有the reference values. In this example, the reference values are the names of the sales staff.
4. If you have other data in this worksheet, such as additional data tables, leave at least one blank row on the bottom of the table and one blank column on the right of the table. This separates the lookup table from other data.

You will encounter some tables with values in the lookup column that are not unique. For example, the table below lacks a unique value forShirtin the first column, as there are three listings. However, the second column lists three different sizes (Small, Medium, andLarge). If you were to order a shirt, you would need the size to specify the price. In reality, the company that sells these products has only one record for each product and size combination.

Follow these steps to create a VLOOKUP for combined values. TheExcel Sample Datafile has pre-existing data. This tutorial shows you how to turn that data into a table that Excel recognizes.

1. Click theVLOOKUP for Combined Valuesworksheet tab in theExcel Sample Datafile.
   
   
2. Insert a column to the left of the Product column to create the Product-Size column, which will combine both the product and size.
   
   
3. Click on cell A3 and type=B3&”-“&C3in the formula bar.
   
   
4. Copy cellA3and paste the formula in all the remaining cells in column A.

This creates a unique value for each entry in column A and allows you to type and use a VLOOKUP function for your data. The VLOOKUP formula for the price of each unique product (in this formula, we are looking for the price of a Shirt-Small) in column A is as follows:

=VLOOKUP(F4,A2:D14,4,FALSE)

To see if it works, click on any cell outside the table on theExcel Sample Datafile sheet, copy the VLOOKUP formula into the formula bar, and clickOK.

For a Shirt-Small, your return value is $15.99.

The following are basic concepts and examples in VLOOKUP that will work with versions of Excel 2007 and later. After you master these concepts, you can then move to more advanced material and make use of the power of VLOOKUP.

How to Do VLOOKUP from Another Worksheet

In reality, very few VLOOKUP formulas are used to look up data in the same worksheet. Most VLOOKUPs pull data from another worksheet.

按照以下步骤执行VLOOKUP程序er worksheet.

1. Click on theVLOOKUP Separate worksheet1worksheet tab.

This data table has the product list downloaded from an accounting program.

2. Click onVLOOKUP Separate worksheet2worksheet tab.

This worksheet has the lookup box. You will draw from theVLOOKUP Separate worksheet1as the data table to populate this worksheet’s lookup box.

3. Click on cellC4inVLOOKUP Separate worksheet2, and type the following formula:

=VLOOKUP(B3,'VLOOKUP Separate worksheet1'!A2:D14,4,FALSE)

Let’s break down this formula:

When you get to the second argument, table_array, you are pulling data from the first worksheet,VLOOKUP Separate worksheet1.To do so, you must tell Excel that your data is on another worksheet, and for the syntax, you’ll start with the name of the worksheet surrounded by single quotation marks (‘), followed by an exclamation point (!), then the cell range:

You can also manually go to the other worksheet and designate the table_array by selecting the range of cells. Excel will automatically recognize that you are on another worksheet and add the correct syntax.

The last two arguments are the same as you’ll usually find in a VLOOKUP. In this case, they are:

4. ClickEnteron the keyboard.

You will also commonly do a VLOOKUP from another workbook.

Follow these steps to execute a VLOOKUP from another workbook:

1. Click on theVLOOKUP Separate workbook1worksheet tab in theVLOOKUP2.2 Sample FileExcel file that you candownload here.

As source data, this worksheet has product data for a clothing manufacturer.

2. Open theVLOOKUP2.2Excel file. There is only one worksheet tab:VLOOKUP Separate workbook2. For this function, both workbooks should be open while you are writing the formula. After the formula has been written, the source workbook does not need to be open. Excel will update its location after you close it in the formula.

This worksheet has the reference data and the lookup box, and it draws data for the VLOOKUP from the first workbook and worksheet.

3. In cellC3of theVLOOKUP Separate workbook2ofVLOOKUP2.2workbook, type the following formula:

=VLOOKUP(B3,'[VLOOKUP2 Sample File.xlsx]VLOOKUP Separate workbook1'!$A$2:$D$14,4,FALSE)

Let’s break down this formula:

When you get to the second argument, table_array, you are looking to pull data from the first workbook,VLOOKUP Separate workbook1. To do so, you must tell Excel that your data is on another worksheet and in another workbook. For the syntax, you’ll add the name of the workbook in brackets[], then the name of the worksheet with an exclamation point (!) before the cell range. Both the workbook name and the worksheet name are surrounded by single quotations (‘):

You can also manually go to the other workbook and designate the table_array by selecting the range of cells for the lookup. Excel will automatically recognize that you are on another workbook and add the correct syntax. When you close the lookup table workbook, your VLOOKUP formula will still work, but it will display the full path for the lookup workbook, as shown below:

The last two arguments are the same as you’ll usually find in a VLOOKUP. In this case, they are:

4. ClickEnteron the keyboard.

If you want to combine data sets, a piece of data must anchor them both. In other words, both sets of data should have at least one piece of data in common, so they can cross from one table to the other.

The following example includes data from the American Community Survey (ACS) on educational attainment in the United States. Age and education levels stratify the data. There are two separate tables, downloaded separately from ACS: one for males and one for females. Since ACS stratifies its data the same way, it is possible to combine these Excel tables to further organize it for your reporting needs.

Excel can help do this with a VLOOKUP, but only because both data sets have a common column. Even if the order of the column was sorted differently, it would not matter, because the data is still present in the first row for each table.

How to Combine Data Sets in Excel

Follow these steps to combine data sets in Excel.

1. For this exercise, there are two worksheet tabs,Education US-MalesandEducation US-Females, in theExcel Sample Datafile. Note: OnEducation US-Males,we added extra column headers so that you can include data from theEducation US-Femalesworksheet. Again, the data on the second sheet does not have to be ordered in the same way if the row headers are present on both sheets in the first column.

2. On theEducation US-Malesworksheet, click on cellJ3and type the following formula:

=VLOOKUP(A3,'Education US-Females'!A2:I35,6,FALSE)

Here’s the breakdown of the formula:

This formula matches theFemales Estimatecolumn in theEducation US-Femalesworksheet, which is column 6 in theEducation US-Malesworksheet. You also need columns 7, 8, and 9. The formulas for loading these into your new table are exactly the same, except for the column numbers in argument three.

3. In cellsK3, L3,andM3 Education US-Malesworksheet, type the following formulas, respectively:

K3=VLOOKUP(A3,'Education US-Females'!A2:I35,7,FALSE)

L3=VLOOKUP(A3,'Education US-Females'!A2:I35,8,FALSE)

M3=VLOOKUP(A3,'Education US-Females'!A2:I35,9,FALSE)

As you can see, the underlined numbers are the only changes to the formula, and they refer to the column number where the data is drawn.

4. To keep the data consistent for the next step, you must add absolute references to the data table (table_array). To do so, click on your first formula in cellJ3, and select and highlight the third argument,'Education US-Females'!A2:I35. On the keyboard, click theF4key. One click adds the absolute reference ($) in the correct place. ClickEnteron the keyboard.

5. Perform step 4 for cellsK3, L3,andM3. Now your cells have absolute references built in, and you can apply their formula to the remainder of your data table.

6. Select and copy cellsJ3throughM3.

7. Paste these cell formulas into the other rows of your table using either theCtrl + Vshortcut or by right-clicking on the mouse and clickingPaste Formulas. With the latter method, you keep the formatting intact.

At this point, your table is complete. You can play with the table and titles, ensuring that they are accurate for your needs. You can also turn the whole data table into an Excel “table” for future use.

In VLOOKUP formulas, you can use wildcard characters to help look up values or spellings you are unsure of:

• Use a question mark (?) to match any single character.
• Use an asterisk (*) to match any sequence of characters.

How to Use Wildcard Characters in VLOOKUP

Follow these steps to use wildcard characters in VLOOKUP.

1. Click on theWildcardstab in theExcel Sample Datafile.

This worksheet has a table of sales team members and their quarter-to-date (QTD) sales. There is aSales QTDlookup box based on the salesperson’s name. Wildcard characters can help in the following scenarios:

• Look up the salesperson’s name starting or ending with certain characters.In this scenario, you are not sure of the exact spelling of Mike Hayes’ name. You can click cellD4(Name lookup box) and typeMike H*. Excel recognizes that the asterisk (*) stands for several missing letters.

You could also add the asterisk to the beginning of Mike’s name to yield the same result or use several asterisks to replace letters you are unsure of.

You could also add the wildcard to your VLOOKUP formula. For example, instead of entering your salesperson’s name in cell D3, you could add what you know into the VLOOKUP formula.

Excel compares your reference value (lookup_value) with the data in the table and matches it, before scanning to your return value. If the value is not present, you will receive an #N/A error. However, even if your value is present in the table, there are still three possible reasons you could get an error:

• A Mismatch in Format:Either your reference value or the table data is formatted as a number and the other is formatted as text. You can fix this error by reformatting so that they match.
• Extra Spaces:Your reference or your table data has leading, lagging, or embedded spaces. To check for extra spaces in a cell, use the LEN function, which returns the number of characters in a cell. To correct for excess leading or lagging spaces, use the TRIM function.
• Other Characters:If the TRIM function does not solve your problem, you could still have other characters in either the reference or data table. Functions that help solve this problem include SUBSTITUTE or CLEAN, or you can add macros. Use SUBSTITUTE to change from characters like the forward slash to blank spaces or empty strings. Use CLEAN to remove nonprinting characters.

Initially, your VLOOKUP may return the correct results. But sometimes you may start seeing incorrect results after you sort the data table. This problem is not unique to VLOOKUP, as it can be found in other lookup functions such as INDEX-MATCH and HLOOKUP. To fix this problem, review your formulas and ensure that they are referencing the correct worksheet. This is an Excel bug that is easy to fix.

Excel 2013 introduced the FORMULATEXT function. Adding FORMULATEXT to the side of your cells enables you to see the formulas in their entirety and to check for errors.

The formulas for the data table are in column C.

1. Click on a cell in row E or F (you can use any cell that doesn’t already have data).
2. Type=FORMULATEXTand then click on the cell with the formula you want to see.

没有w you can take a closer look at the formula and see what needs to be changed without affecting the original cell.

本文的Excel VLOOKUP基础知识会有所帮助you build up to the intermediate material in the next sections. Some details to remember:

• 你可以以任何方式排序表,但你的左边column should not have duplicate values.
• Use absolute cell references ($) in your table_array argument to ensure that you can copy and paste your formula to other cells.
• ClickF4to add absolute references to your table_array.
• VLOOKUP formulas read from left to right.
• In approximate match lookups, make sure your lookup value is larger than the smallest value you have identified. Otherwise, you will return an #N/A error.
• In approximate match lookups, always sort the data in ascending order in the data table (table_array).
• Values in VLOOKUP formulas are not case-sensitive, so you do not have to worry about uppercase or lowercase when typing formulas.
• Your col_index_num cannot be less than one (1). One signifies the first column.
• VLOOKUP can only look to the right in its search.
• The last argument, [range_lookup], is automatically set to TRUE (approximate match), unless you specify it as FALSE (exact match).
• The VLOOKUP formula works the same in all versions of Microsoft Excel.
• The #N/A error means that Excel cannot find the value you are seeking.
• The table data you are searching for may be on the same worksheet, on a different worksheet, and even in a different workbook.

As you gain more experience with VLOOKUP, you will want to perform more complex functions. Before you move on, review the basic syntax and usage of VLOOKUP by readingthis article.

How to Combine VLOOKUP and HLOOKUP Functions

按照以下步骤结合VLOOKUP, HLOOKUPfunctions.

1. Click theMedals Exampleworksheet tab in theExcel Data Sample File.

This worksheet has a matrix of data with headers in both the row across the top and the column along the left. This is a fictitious table of gold, silver, and bronze medals earned in the 2012 Summer Olympics by each country. To the right side of the data table are both VLOOKUP and HLOOKUP boxes to enter the country and type of medal. Type the VLOOKUP with a nested HLOOKUP formula in the return value box (F8).

2. In the vertical lookup box, typeGermany, and in the horizontal lookup box, typeBronze. This gives you values to follow in this example.

3. Insert a blank row above row 5. Since VLOOKUP is only capable of referring to a number in the col_index_num, type numbers2, 3,and4in cellsB5, C5,andD5, respectively.

Type=VLOOKUP(in cellF8to signal to Excel that you are starting a VLOOKUP function.

4. As with the earlier VLOOKUP formula example, add the first two arguments separated by commas below:

So far, your formula is=VLOOKUP(F5,A3:D28.

5. Add the second function in the third argument, col_index_num. Instead of inserting a column number, add HLOOKUP as a nested function in a new set of parentheses, like a formula within a formula. TypeHLOOKUP(to signal to Excel that you are starting another function.

6. Add the four HLOOKUP functions:

7. Add the last VLOOKUP argument,FALSE.

8. ClickEnteron the keyboard.

How to Combine VLOOKUP with MATCH

Combining the VLOOKUP and MATCH functions enables you to construct a flexible lookup formula that is easier to copy across a worksheet. The MATCH formula replaces col_index_num in the VLOOKUP formula. This combination also helps prevent problems when you add new columns to the lookup table or if you rearrange the columns. This scenario is similar to using a VLOOKUP-HLOOKUP combination, except you do not need a proxy number for your row.

Follow these steps to perform a VLOOKUP-MATCH.

1. Click theVLOOKUP-MATCH Exampleworksheet tab.

This worksheet has a data table that is a matrix with headers in both the top row and the first column. There are VLOOKUP and MATCH boxes to help you visualize where the formula draws its data. The return value box (F8) is where you will type the VLOOKUP with the nested MATCH formula.

2. NoticeGermanyin the VLOOKUP box andBronzein the MATCH lookup box. This provides values to follow along with in this example.

3. Like the VLOOKUP-HLOOKUP combination, VLOOKUP-MATCH replaces the third argument (col_index_num) in the VLOOKUP formula. This is a nested MATCH function. The overall formula in cellF8will be:

=VLOOKUP(F5,A4:D28,(MATCH(G5,A4:D4,0)),FALSE)

Excel performs the internal function (MATCH) first, then the VLOOKUP function.

The MATCH formula nested in the overall formula is:

Place this formula (nested) inside the VLOOKUP formula, replacing the third argument. The VLOOKUP formula is:

4. Click cellF8and type=VLOOKUP(.

5. Type the first two arguments in the VLOOKUP formula,F5andA4:D28, separated by a comma.

6. Type the MATCH formula,(MATCH(G5,A4:D4,0)), as the third argument in the VLOOKUP function.

7. Type a comma (,) and the last VLOOKUP argument,FALSE. Close the combined function with a parenthesis ). ClickEnteron the keyboard.

As you can see, in this data table, Germany won 14 bronze medals. If you change both the values in the VLOOKUP and MATCH boxes, you consistently get the correct return value from the data table.

How to Combine IF and VLOOKUP Functions

When you add the IF function to the VLOOKUP, the cell remains blank instead of showing an error if your VLOOKUP does not have a value to return.

Follow these steps to add an IF function to your VLOOKUP.

1. Click on theVLOOKUP-IF Examplesample file.

This worksheet shows geography and the population from the 2010 United States Census. Without any value specified in theGeographyVLOOKUP box, the return value in thePopulationbox displays the #N/A error. If you typeGuamin cellD4, you would also receive this error.

The current formula for this VLOOKUP is=VLOOKUP(D4,A2:B55,2,FALSE), which works well for values found in the data table.

2. To combine the VLOOKUP and the IF function, wrap the VLOOKUP with the IF formula using the ISNA function to check for an #N/A error. The format for an IF function is:

IF(something is true, do something else, do something else because the comparison is false)

IF statements are logic tests that can have two different results based on whether a comparison is true or false. ISNA checks for a #N/A error. The logic for this IF statement is:

如果有一个# N /错误,输入空白单元,其他wise enter the VLOOKUP result)

Your VLOOKUP statement is used twice in this formula. Type the following arguments in cell D4:

=IF(ISNA(VLOOKUP(D4,A2:B55,2,FALSE)),"",VLOOKUP(D4,A2:B55,2,FALSE))

Here is the IF formula broken down into its three arguments:

3. ClickEnteron the keyboard.

注意:When entering aGeographicarea that is not in the data table, thePopulationcell still returns a blank cell.

In Excel 2007, Microsoft added the IFERROR function. Compared to the above IF-ISNA function, the IFERROR function requires less time and computing power. In the IF-ISNA function above, you ran the VLOOKUP once to see if it failed, and if it did not fail, you ran it again. The IFERROR function does the lookup only once and allows you to specify what it does if it fails.

The syntax of the IFERROR function is as follows:

=IFERROR(value,value_if_error)

So if Excel checks the value and returns an error, you can choose what value to display. Excel checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

Follow these steps to combine the IFERROR and VLOOKUP functions.

1. Click theVLOOKUP-IFERROR Exampleworksheet tab in theExcel Sample Datafile.

This worksheet shows the data from the 2010 Census by geography with no value specified in theGeographybox. But thePopulationbox shows an #N/A error.

2. In cellE4, wrap the current VLOOKUP formula with the IFERROR formula. The VLOOKUP formula is currently=VLOOKUP(D4,A3:B55,2,FALSE). To wrap this with an IFERROR function, type the following arguments around the VLOOKUP:

=IFERROR(VLOOKUP(D4,A3:B55,2,FALSE),"Not Found")

Here are the two arguments in the IFERROR formula:

3. ClickEnteron the keyboard.

In this example, you have specified that the return value in case of an error is没有t Found. The same return would apply if you entered another geography not listed in the table, such asBrazil.

One of the prime requirements of the VLOOKUP function is that your main data table and your lookup table have data in common. However, a table occasionally translates and acts as an intermediary for this data. See the following three tables:

Table 1 has the same column of data available in Table 2, so Table 2 can fill in the data for Table 3 as an intermediary. This is the purpose of nesting formulas: They can draw data from each other.

You must use VLOOKUP in an intermediary step to fill in the data you seek for your final table. Without Table 2, you cannot answer your query. Practically, you are using Table 2 to look up the matching ID to the Paycode and match the Paycode to the Name from Table 1. You will enter the VLOOKUP formula in the Paycode column of Table 3.

Follow these steps to perform a nested VLOOKUP with multiple criteria.

1. Click on theNested VLOOKUPworksheet tab in theExcel Sample Datafile.

2. Type your first VLOOKUP formula in cellB13:

=VLOOKUP(VLOOKUP([@Name],Table1[#All],2,FALSE),Table2[#All],3,FALSE)

The Tables in this Excel worksheet have been named and saved asTables 1, 2,and3. The practice of naming the tables changes the cell name in your formulas to the table titles. For example, cell A13 is [@Name] in the formula above. When you specify the whole of a table in Excel formulas, Excel writes it asTable1[#All]. Breaking down this formula, Excel performs the first VLOOKUP first. Nested formulas (those in parentheses) are calculated first. The first formula is:

=VLOOKUP([@Name],Table1[#All],2,FALSE

Without the additional VLOOKUP formula surrounding this VLOOKUP function, your return value would show12394. However, you wrapped the internal VLOOKUP with another VLOOKUP formula(=VLOOKUP(Return value from first VLOOKUP function),Table2,3,FALSE). Therefore, your return value in cellB13isA.

3. Copy cellB13and usePaste Formulato insert it into the reminder of the cells in thePaycodecolumn forTable 3. ClickEnteron the keyboard.

没有tice that the formula recognizes that the data is part of a defined table in Excel and does not change based on its cell. This shows the flexibility of Excel tables.

You have already learned that Excel can return only the first matching value in a VLOOKUP. However, some Excel experts want to return other or all occurrences of the matching values. That is, they want to find each unique occurrence, though the data appears to be duplicate. For example, a database has the last several years of orders from a clothing company. Some of the orders are repeats, although they were done at different times and may have been for different items.

Use the following method to make each record unique and show these multiple occurrences. VLOOKUP returns only one value at a time to each cell, but you can make it so that each duplicate becomes unique.

Follow these steps to return other occurrences of the matching value.

1. Click on theMultiple Occurrencesworksheet tab in theExcel Sample Datafile.

This worksheet displays a sales team and their individual orders to a merchandiser.Column Adoes not have unique values in every row. The merchandiser wants to know the subsequent purchases for each person.

2. Add a new column to the left of theNamecolumn (Column A) by right-clickingAat the head of the first column and clickingInsert.A new column A is created and the remainder of the table moves to the right.

3. Type the following COUNTIF formula in cellA2:

=MerchOrders[@Name]&COUNTIF($B$2:B2,B2)

Here’s the breakdown for the formula:

4. Copy cellA2and use thePaste Formulaoption to copy it down the table.

5. Name columnA Name-Unique.

6. Click anywhere in the table to reveal theDesigntab. Click theDesigntab and clickResize Table. This action opens the Resize Table pop-up box. Now, you can add the first column into your predefined table by changing the first letter fromBtoAas shown below.

7. ClickOK.

8. Use the regular VLOOKUP formula to find your orders. In cellF4, type:

=VLOOKUP(F3,Table6[#All],3,FALSE)

没有te: The VLOOKUP formula would not be helpful for retrieving all the matching values for this at once.

Using the INDIRECT function with the VLOOKUP returns a range. Use this shortcut if you do not want to constantly change the range of cells when updating your worksheets. In this combined function, you are pulling data from multiple worksheets that have the same formatting. For example, you could use this to combine reports. Normally, you can reference only one additional worksheet in VLOOKUP, but adding the INDIRECT function allows you to reference more worksheets, as long as they have an identical setup.

Follow these steps to combine the VLOOKUP and INDIRECT functions.

1. Click on theINDIRECT-Summaryworksheet tab in theExcel Data Samplefile. You also have two additional worksheets for this exercise:Store 1 InventoryandStore 2 Inventory. The naming of the worksheets is crucial, as you need to replicate those names exactly on the Summary worksheet.

2. On theINDIRECT-Summaryworksheet, notice that the two column headers located in cellsC2andD2match the names of the two worksheets you are pulling data from. You are looking to pull the different stores’ inventory numbers into one tracking sheet:INDIRECT-SUMMARY.

3. In cellC3of theINDIRECT-Summaryworksheet, type the following formula:

=VLOOKUP($B3,INDIRECT("'"&C$2&"'!"&"$B$2:$C$8"),2,FALSE)

The VLOOKUP formula is the same as the one you have been writing up to this point. The first argument (Lookup_value) isProduct 1with absolute references$on the column letter, but not on the row number so that the formula can tile down but not across to the right.

间接功能位于第二个参数ument (table_array). INDIRECT functions have two components: the reference text (ref_text) and A1 [a1]. The A1 component is optional and defaults to TRUE when omitted, meaning the text referenced is an A1-style reference. The reference text in this formula is:

("'"&C$2&"'!"&"$B$2:$C$8")

This creates a range reference to the cells where we want to pull the data. Here is a breakdown of their functions:

• “ ‘ “:A single quote within double quotes (with spaces added for clarity) precedes the sheet name in case there are space characters in the worksheet name.
• &:Concatenate.
• C$2:The absolute reference to cell C2, which is the name of the worksheet we are referencing.
• “!”:The exclamation point surrounded by double quotes means we are looking for another worksheet.
• &:Concatenate.
• "$B$2:$C$8":The data table’s address for the data return we are seeking.

4. ClickEnteron the keyboard.

5. Copy cellC3and use thePaste Formulafunction in the remainder of the Summary table.

Many users like and are comfortable with VLOOKUP because they learned it early in their Excel education. It also performs the function they need. INDEX-MATCH is another lookup function when you nest functions. Here are three reasons why VLOOKUP is not the best choice for this use, and INDEX-MATCH is a better option for a lookup function:

• You need a formula that can look right to left.
• You have an enormous number of columns.
• Your spreadsheet computation is too slow.

The MATCH function searches for a specific item in a cell range and returns the position of that item. The syntax isMATCH(lookup_value,lookup_array,[match type]). Use this function alone if you need the position of an item, but not the actual item.

The INDEX function provides the value within a table, range, or array. The syntax for an INDEX function isINDEX(array,row_num,[column_num]). You can use this syntax in a two-dimensional lookup, as it gives you the opportunity to specify both the row and column location. In a nutshell, INDEX provides the value located at an exact position.

Follow these steps to use INDEX-MATCH for a lookup.

1. Click on the INDEX-MATCH worksheet tab in theExcel Data Samplefile.

正如你所看到的,有一个销售团队的名单s quarterly sales to date (Sales QTD).

2. To perform a lookup with INDEX-MATCH, type the following formula in cellE3:

=INDEX(B2:B10,MATCH(D3,A2:A10,0),1)

Breaking down this formula, you have the MATCH nested in the INDEX formula. Excel performs this function first:

=MATCH(D3,A2:A10,0)

For the INDEX function:

=INDEX(B2:B10,MATCH,1)

3. ClickEnteron the keyboard.

Google Sheets is an Excel competitor, but allows more collaboration options as it was born in the cloud. Many functions in Excel are also in Google Sheets, and many Excel spreadsheets may be converted to Google Sheets and vice versa. However, you cannot use one program as a reference for the other. For example, in the case of VLOOKUP, you may not add an Excel spreadsheet reference into the formula on your Google Sheet and expect it to pull data from that Excel sheet.

Certain functions in Google Sheets work in the same way they do in Excel. VLOOKUP is one of those functions.

How to Perform a VLOOKUP from a Different Google Sheet

Follow these steps to perform a VLOOKUP from a different Google Sheet.

1. Click onVLOOKUP Separate worksheet1worksheet tab in theGoogle Sheet Sample file. (Note: Some formulas shown in this tutorial already appear in theGoogle Sheet Sample file.) For this exercise, you also have aVLOOKUP Separate worksheet2to use.

2. Type the following formula into cellC3onVLOOKUP Separate worksheet2:

=VLOOKUP(B3,'VLOOKUP Separate worksheet1'!A2:D14,4,FALSE)

Let’s break down this formula:

To signify that you are looking at data from another sheet, the sheet name for the Range is surrounded by single quotes (‘) and followed by an exclamation point (!).

Google Sheets does a few things differently than Excel:

• The formula in the overhead bar is colored to match the formula in the cell and remains that color.
• Google Sheets provides a return value as you type the formula in a call-out box above the formula.
• Google Sheets automatically saves your work and updates from multiple users in real time, and it supports VLOOKUP with wildcard characters.
• The formulas are defined slightly differently than those in Excel. They work in the same manner, but they are meant to be more intuitive to understand.

Smartsheet is a work management platform that allows you to use higher-level functions across your sheets. Since Smartsheet does not store sheets in the traditional “workbook” format, you can reference any sheet you can access with your login address.

Follow these steps to perform a VLOOKUP across sheets.

1. Click on the SmartsheetVLOOKUP_Smartsheet1and view. This sheet has information imported from a product database on products and their correlating prices. For this exercise, see alsoVLOOKUP_Smartsheet2, which has designated space for a VLOOKUP for this example. In this sample, you want to look up data fromVLOOKUP_Smartsheet1and add it toVLOOKUP_Smartsheet2using the VLOOKUP formula.

2. With Smartsheet, you can either type in the whole formula or use the function button that Smartsheet located in the Numbers section on the sheet. In this example, you will use theFunctionbutton. Open theVLOOKUP_Smartsheet2sheet and click on thePrice2cell, then click on the drop-down arrow next to theFunctionbutton. ClickAdvanced FunctionsandVLOOKUP. You can also find the VLOOKUP function underFunctions/All Functions.

The VLOOKUP formula loads into thePrice2cell, with notes on syntax and examples.

The syntax for VLOOKUP in Smartsheet is as follows:

=VLOOKUP(search_value,lookup_table,column_num,[match_type]

The VLOOKUP method in Smartsheet is similar to that of Excel and Google Sheets, but is more intuitive and user-friendly. The currently required argument in the formula is highlighted. As with Excel, you can select the cells where your current argument is located or type the argument directly into the formula. Separate each argument by typing a comma (,).

3. The formula for this function is the following:

=VLOOKUP(Product2,{PriceTable},4,false)

Select cellProduct2for the first argument and follow the selection with a comma (,).

4. Once you type the comma after the first argument, the second argument is highlighted in the help pop-up box. To access a data table that is present on another sheet, you must click theReference Another Sheethyperlink.

TheReference Another Sheetpop-up box appears. Choose from all the sheets that your Smartsheet email allows you to access. You can choose data from any of these sheets. For this example, chooseVLOOKUP_Smartsheet1.

5. ClickVLOOKUP_Smartsheet1. To select your table range, Smartsheet recommends clicking the column header of the columns you want to include. To select multiple columns, hold down theShiftorCtrlkey on the keyboard and click it with your mouse. Click and highlight all the cells. Once the cell range is highlighted, you can also change the name of the range in theSheet Reference Namebox to make it easier to read in your formula. For this exercise, replace VLOOKUP_Smartsheet1 Range 6 withPriceTable.When you name your range, remember that Smartsheet does not allow you to submit a name of a cell or table that is already in use in that sheet.

6. ClickInsert Referenceon theReference Another Sheetpop-up box. This action closes the pop-up and returns you toVLOOKUP_Smartsheet2.

Since you are referencing a data table that comes from another sheet, the lookup_table reference is surrounded by curly brackets{}.

7. You can type the last two arguments in this function manually. Close your second argument with a comma (,) and type 4, thenfalse. The last argument must be lowercase.

8. Type the close parenthesis ) and clickEnter在键盘上完成这个功能。

For more information about VLOOKUP function across sheets, watch thisvideo.

In Smartsheet, the following are best practices and tips for using formulas across sheets:

• Use cross-sheet formulas on any sheet you can access.没有te: You must have at least editor access on the destination sheet and viewer access on the source sheet.
• Reference the entire column in your formula for accuracy.This way, if your data table changes, Smartsheet can recognize the parameters in your formulas, and the formulas will stay intact.
• Custom-name ranges in cross-sheet formulas:This practice allows you and colleagues viewing your formulas to better identify the ranges selected.
• Know your limits.A maximum of 25,000 cells is available for cross-sheet references in these formulas in Smartsheet. This is significantly larger than past cell links maximums, which were 5,000.
• Use cell links when calculations are not necessary.Cell links enable users to input data from a cell in one sheet into another sheet, without the benefit of a calculation. If you need to use calculations with your data to perform any number of higher-level functions, go with functions such as VLOOKUP, SUMIF, or COUNTIF.

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time.Try Smartsheet for free, today.