=VLOOKUP,C:UsersAdministratorDesktop［工作簿1
问题：避免VLOOKUP函数返回#NA错误的方法
回答：在Excel中经常使用VLOOKUP函数来查找某个数据区域的第一列，并返回所查找数据在指定列中对应行的单元格值。在用VLOOKUP函数进行精确查找时，如果要查找的值不在数据区域的第一列中，VLOOKUP函数会返回#N/A错误。例如下图数据在...
问题：求EXCEL表VLOOKUP函数使用高手。
回答：你好,有什么需要帮助的,请说.介绍一个VLOOKUP的公式发,说明我虽说不上是高手,但还是略知一二:求A列中数字的最后一行数字是多少(不包括文)公式:=VLOOKUP(MAX(A:A)+1,A:A,1)如图:
问题：类似VLOOKUP查找带星号文本的公式
回答：大家知道，在Excel中用VLOOKUP函数进行查找时，如果第四个参数为FALSE且查找的内容为文本时，就可以使用通配符——问号 (?) 和星号 (*)。但如果所查找的文本中本身包含星号，直接使用VLOOKUP函数可能不会得到正确的结果。例如在...
问题：Excel中VLOOKUP函数的应用及vlookup函数实例的下载
回答：点评：VLOOKUP函数是指纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，VLOOKUP是按列查找，最终返回该行所需查询列序所对应的值；HLOOKUP是按行查找的。
法则： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数&nbsp...
问题：与VLOOKUP函数相反的左向查找公式
回答：大家知道，在Excel中可以利用VLOOKUP函数根据区域中第一列的某个数据查找出其他列中对应行的数据。例如，在下图中要查找对应“值班日期”为“初五”的“姓名”，可以用公式：
=VLOOKUP(A6,A1:D7,2,0)
但VLOOKUP函数只能向右查找...
问题：用VLOOKUP函数在两列中查找
回答：大家知道，Excel中的VLOOKUP函数可以在区域的第一列中进行查找，然后返回该区域中相同行上的单元格值。但如果需要在两列或多列中查找该怎样操作呢？例如下图中要查找“黑色”的“袜子”所对应的销售数量。
实际上，可...
相关知识：
为您推荐：
在表格中B列是日期，E列是金额。怎么样...
请高手解惑： 这个要编写VBA代码，是一...
CRM系统是国内的好还是国外的好，各有...
在图层上右键-创建剪帖蒙板，快键方法...VLOOKUP Examples And Videos
How to use a VLOOKUP formula to get data from an Excel list. Troubleshoot VLOOKUP when things go wrong. Step-by-step videos and free workbooks with VLOOKUP examples.
VLOOKUP Video
Watch the steps for creating this formula in the Product
Price VLOOKUP video, show below. The written instructions are below
the video. Get the
to follow along with the video.
Select a Location for a Lookup Table
It's a good idea to store each lookup table on a separate worksheet
in the workbook. Then, as you add and delete rows in the lookup tables,
you won't accidentally add or delete rows in any other table.
In this example, the lookup table is on a sheet named Products.
Create a Lookup Table
Lookup formulas can work vertically, looking for values down a column,
or they can work horizontally, looking for values across a row. In
this example, the information will be stored vertically, with values
down a column, and later we'll use a VLookup formula to do a vertical
Enter the headings in the first row
The first column should contain the unique key values on which
you will base the lookup. In this example, you can find the price
for a specific product code.
If you have other data on the worksheet, leave at least one blank
row at the bottom of the table, and one blank column at the right
of the table, to separate the lookup table from the other data.
Note: To make it easier to refer to the table, you can name the range.
There are instructions here: &&
VLOOKUP Function Arguments
The VLOOKUP function has four arguments:
lookup_value: What value do you want to look up? In this
example, the product code is in cell A7, and you want to find its
product name.
table_array: Where is the lookup table? If you use an absolute
reference ($A$2:$C$5), instead of a relative reference (A2:C5),
it will be easier to copy to formula to other cells. Or, name the
lookup table, and refer to it by name.
col_index_num: Which column has the value you want returned?
In this example, the product names are in the second column of the
lookup table.
[range_lookup]: Do you want an exact match? Is an approximate
match okay?
If you use TRUE as the last argument, or omit the last argument,
an approximate match can be returned. This example has FALSE as
the last argument, so if the product code is not found, the result
will be #N/A. (Note: Excel is rather forgiving, and will accept
0 instead of FALSE, and 1 instead of TRUE.)
Create a VLookup formula
Once you have created the lookup table, you can create other formulas
in the workbook, and pull information from the product list. For example,
to prepare an invoice, you can enter a product code, and formulas
will get the product name or price from the product table.
In this example, the invoice is created on a sheet named Invoice.
The VLOOKUP formula should find an exact match for the product code,
and return the product name.
To create the VLOOKUP formula that calculates the product price,
follow these steps:
Select the Invoice sheet
Enter product code A23 in cell A7
In cell B7, start the VLOOKUP formula:
&&=VLOOKUP(
Click on cell A7 to enter its reference.
Type a comma, to end the first argument
Select the Products sheet
Select cells A2:C5 (the product list)
Press the F4 key, to change the cell references to absolute. The
formula should now look like this:&
&& =VLOOKUP(A7,Products!$A$2:$C$5
Type a comma to end the second argument.
Type a 2, which is the column in the lookup table that
contains the Product name.
Type a comma to end the third argument.
Type FALSE, to specify that an exact match for the product
code is found, and add the closing bracket.
The formula should now look like this:&
&& =VLOOKUP(A7,Products!$A$2:$C$5,2,FALSE)
Press the Enter key to complete the formula. The product name
will be displayed.
Note: To return the product price, create a VLOOKUP formula
that refers to column 3 of the lookup table. For example, enter the
following formula in cell C7:
&&&&&&&&&&=VLOOKUP(A7,Products!$A$2:$C$5,3,FALSE)
VLOOKUP Formula for Range of Values
In some situations, an approximate match is preferred, so several
values will return the same result. For example, when grading student
papers, all papers with a grade of 85 or over should receive an A
To view the steps for creating this formula, please watch the VLOOKUP
video shown below. The written instructions are below the video.
In this example, the lookup table is created on a sheet named Grades.
To create the lookup table, enter the minimum score for each grade
in column A. Enter the matching Grade in column B. Sort the Scores
in Ascending order.
Cells A2:B6 were named GradeList.
The scores are entered on a sheet named Report Card,
where a VLOOKUP formula calculates the grade.
On the Report Card sheet, in cell B4, enter the score 77.
In cell C4, enter the VLOOKUP formula:
&&=VLOOKUP(B4,GradeList,2,TRUE)
Press the Enter key, and the grade for English -- B -- is returned.
In the screen shot below, the formula has been copied down to row 6,
and the you can see the formula in cell C6.
Combine VLOOKUP With MATCH
Instead of typing the column number into a VLOOKUP formula, use the function to find the correct column in the lookup table. This has a couple of benefits:
Makes the formula flexible, so it's easier to copy the formula across a worksheet.
Can prevent problems if new columns are added in the lookup table, or if the lookup columns are rearranged.
This video shows the steps, and there are written instructions for another example, below the video.
Your browser can't show this frame. Here is a
to the page
VLOOKUP With MATCH for Order Details
In this example, a
VLOOKUP formula will return the order details from a lookup table, based on the order ID number. Here is the lookup table, named tblOrders.
NOTE: This example is in
, on the sheet named OrdersMATCH.
Here is the worksheet with the VLOOKUP formulas. We want the Region, Order Date and Order Amount for each order, so 3 VLOOKUP formulas are needed.
If the column numbers are typed in the formula, a different formula is needed in each column:
Region: =VLOOKUP($B6,tblOrdersALL,2,0)
OrderDate: =VLOOKUP($B6,tblOrdersALL,3,0)
OrderAmt: =VLOOKUP($B6,tblOrdersALL,4,0)
The MATCH Function
Instead of typing the column number in the VLOOKUP formula, we can use the MATCH function. The MATCH function
finds the position of an item in a list, and returns the position number.
In the screen shot below, the MATCH formula returns 2 as the position of &Region&, in the heading cells (A1:D1) for the lookup table.
=MATCH(C5,Orders_ALL!$A$1:$D$1,0)
NOTE: For this technique to work correctly, the headings on the VLOOKUP sheet must match the lookup table headings exactly. To ensure an exact match, the VLOOKUP heading cells are linked to the lookup table heading cells.
Add MATCH to VLOOKUP
To add the MATCH function to the VLOOKUP formula, just replace the typed column number
=VLOOKUP($B6,tblOrdersALL,2,0)
with the MATCH formula:
=VLOOKUP($B6,tblOrdersALL,MATCH(C5,Orders_ALL!$A$1:$D$1,0),0)
Copy the VLOOKUP Formula Across
Now, instead of needing a different formula in each column, you can copy the formula across, and use the same formula in all the columns. In each column, it will refer to the heading cell in that column, and find its position in the lookup table.
NOTE: If you are filling the formula across columns with different formatting, follow these steps:
Select the
cell with the formula that you want to copy
Point to the fill handle on the selected cell (small square at the bottom right corner)
Press the RIGHT mouse button, and drag across to the last cell that needs the formula
Release the mouse button, and click on Fill Without Formatting
Combine IF and VLOOKUP
You can use an IF formula with a VLookup formula, to return exact
values if found, and an empty string if not found.
To see the steps for setting up the IF and VLOOKUP formula, you can
watch this short video. The written instructions are below the video.
To hide errors by combining IF with VLOOKUP, follow these steps:
On the Invoice sheet, in cell A8, enter the product
code A28. If the VLookup formula in cell B8 has FALSE as the fourth
argument, the result is #N/A, because there is no exact match
for the product code in the lookup table.
Wrap the VLookup formula with an IF formula (in this example the
product list has been named), using the ISNA function to check for
an #N/A error:
&&=IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",VLOOKUP(A8,ProductList,2,FALSE))
Press the Enter key, and cell appears blank. Because no exact
match was found, the VLookup formula returned an #N/A, so the ISNA
function result is TRUE. The IF formula converted this to an empty
If the lookup table contains any blank cells, a VLOOKUP formula will
return a zero, instead of a blank cell. You can use nested IFs to
handle the #N/A results, and the empty cell results. For example:
&&&&&&=IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",
&&&&&&&&&&&
IF(VLOOKUP(A8,ProductList,2,FALSE)="","",
&&&&&&&&&&&&&
VLOOKUP(A8,ProductList,2,FALSE)))
Combine IFERROR and VLOOKUP
Thanks to Chip Pearson for suggesting this formula.
In Excel 2007, a new function, IFERROR, was introduced. You could
use an IFERROR formula with VLookup to check several tables for a
value, and return the related information when found. In this example,
three regions, West, East and Central, have order sheets. On each
sheet is a named range -- OrdersW, OrdersE and OrdersC.
On a sheet named Orders, you can enter an Order ID, then use a VLOOKUP
with IFERROR to check each named range, and view the information about
the selected order.
On the Order sheet, in cell B6, enter a 4 as the
OrderID. That order was placed in the Central region.
To simply check the East region's table, the VLOOKUP formula in
cell C6 would be:
&&=VLOOKUP(B6,OrdersE,2,FALSE)
Press the Enter key, and the VLOOKUP formula returns an #N/A,
because Order ID 4 is not in the East regions order table.
Because an order could have been placed in any of the three regions,
you need a formula that will check each table.
If the order ID is not found in the first table, the formula should
check second table. If the order ID is not in the second table,
it should check the third table. If the order ID isn't in the third
table, then a &Not Found& message should appear in the
The IFERROR formula lets you check a value, then specify what
to do if an error is found. If you use IFERROR with the existing
formula, you can show &Not Found&, instead of the #N/A
&=IFERROR(VLOOKUP(B6,OrdersE,2,FALSE),"Not Found")
To check all three tables, you can next IFERROR and VLOOKUP formulas:
&&&&&&=IFERROR(VLOOKUP(B6,OrdersE,2,FALSE),
&&&&&&&&&&&&&&IFERROR(VLOOKUP(B6,OrdersW,2,FALSE),
&&&&&&&&&&&&&&&IFERROR(VLOOKUP(B6,OrdersC,2,FALSE),"Not
This checks the OrdersE table and if an error is found, checks OrdersW
table, then OrdersC. If the OrderID is not found in any of the three
tables, the Not Found message is shown in the cell.
You can also check multiple tables in older versions of Excel, where
IFERROR is not available, using a longer formula:
&&&&&&=IF(NOT(ISERROR(VLOOKUP(B8,OrdersE,2,FALSE))),
&&&&&&&&&&&&&&&&VLOOKUP(B8,OrdersE,2,FALSE),
&&&&&&&&IF(NOT(ISERROR(VLOOKUP(B8,OrdersW,2,FALSE))),
&&&&&&&&&&&&&&&
VLOOKUP(B8,OrdersW,2,FALSE),
&&&&&&&&IF(NOT(ISERROR(VLOOKUP(B8,OrdersC,2,FALSE))),
&&&&&&&&&&&&&&&
VLOOKUP(B8,OrdersC,2,FALSE),"Not Found"))))
VLOOKUP for Combined Values
In some tables, there might not be unique values any column in the
lookup table. For example, in the table shown below, Jacket is listed
twice in column A. However, there is only one record for each jacket
and size combination -- Jacket Medium in row 4 and Jacket Large in
If you need to find the price for a large jacket, a VLOOKUP based
only on column A would return the price for the first jacket listed
(Medium). You would be underpricing the jacket -- selling it for 60.00,
instead of 65.00.
To create unique lookup values, you can insert a new column at the
left side of the table, and use a formula to combine the product and
size. In cell A2, the formula combines the value in B2 and the pipe
character and the value in C2.
=B2 & "|" &C2
Copy that formula down to the last row of data, so each row has a
unique value in column A.
Note: Instead of the pipe character, you could use another character
that isn't included in your data.
Then, in a VLOOKUP formula, combine the product and size as the Lookup_value.
In cell H1, the formula combines the value in F1 and the pipe character
and the value in G1.
=VLOOKUP(F1 & "|" &G1,$A$2:$D$5,4,FALSE)
Note: The price is now in column 4, instead of column 3.
Troubleshoot the VLOOKUP formula
Your VLOOKUP formula may return an #N/A, even though the value you're
looking for appears to be in the lookup table.
Text vs. Number
A common cause for this error is that one of the values is a number,
and the other is text. For example, the lookup table may contain '123
(text), and the value to look up is 123 (a number).
If possible, convert the text to numbers, using one of the methods
shown here:
If you can't convert the data, you can convert the lookup value within
the VLOOKUP formula:
Lookup values are Text, and the table contains Numbers
If the lookup table contains numbers, and the value to look up is
text, use a formula similar to the following:
=VLOOKUP(--A7,Products!$A$2:$C$5,3,FALSE)
The double unary (--) converts text to a number, and will work correctly
even if the lookup values are numbers.
Lookup values are Numbers, and the table contains Text
If the lookup table contains text, and the value to look up is numeric,
use a formula similar to the following:
=VLOOKUP(A7 & &&),Products!$A$2:$C$5,3,FALSE)
=VLOOKUP(TEXT(A7,&00000&),Products!$A$2:$C$5,3,FALSE)
The TEXT function converts a number to text, and will work correctly
even if the lookup values are text. In the first example, the &
operator creates a text string from an unformatted number. In the
second example, a number formatted with leading zeros (e.g. 00123)
would match a text &number& with leading zeros.
To see the steps for fixing the VLOOKUP problem when the lookup table
has text values, watch this short video tutorial.
Spaces in one value, and not the other
Another potential cause for no matching value being found is a difference
in spaces. One of the values may contain leading spaces (or trailing,
or embedded spaces), and the other doesn't. To test the values, you
can use the LEN function, to check the length of each value.
For example: &&=LEN(A7) &&& will return
the number of characters in cell A7. It should be equal to the number
of characters in the matching cell in the lookup table.
If possible, remove the unnecessary spaces, and the VLOOKUP formula
should work correctly. If you can't remove the spaces, use the TRIM
function in the VLOOKUP, to remove leading, trailing or duplicate
spaces. For example:
&&&&=VLOOKUP(TRIM(A7),ProductList,2,FALSE)
Fix Problems With SUBSTITUTE and TRIM
If TRIM function alone doesn't solve the problem, you can try adding
the SUBSTITUTE function, to remove unwanted characters. There is an
example on the Contextures blog:
CLEAN the Data
Another way to fix VLOOKUP problems is with the CLEAN function, which
can remove some unwanted characters from the text. There is more information
on the CLEAN function in this Contextures blog post:
HTML characters in one value, and not the other
If you copied data from a web page, it may contain non-breaking space
(&nbsp) characters. David McRitchie has written a macro to remove
them, along with other spaces characters -- &&
Problems When Sorting VLOOKUP formula
A VLOOKUP formula may return the correct results at first, but then shows
incorrect results if the list of items is sorted. This can occur if the
reference to the Lookup value includes a sheet name. For example:
=VLOOKUP('Order Form'!B5,Products!$B$2:$C$6,2,FALSE)
NOTE: This problem can occur with other functions too, such as an INDEX/MATCH
lookup formula.
Watch this video to see the steps for fixing the problem, and download
to follow along. The .
Your browser can't show this frame. Here is a
to the page
Sheet Names in Reference
This type of reference is created if you click on another sheet while
building the formula. As soon as you do that, Excel adds the sheet name
to any subsequent references in the formula.
In the screen shot above, Dress is in cell B9, and cell C9 shows the
correct price of $30.
However, after sorting the products A-Z, the Dress moves up to cell B5,
but the formula in cell C5 continues to refer to cell B9. Because of the
sheet names in the references, Excel retains the original references,
instead of keeping a reference to the current row. Cell C5 is showing
the price for a Sweater, instead of a Dress.
Fix the Problem
To solve the problem, remove any unnecessary sheet names from the VLOOKUP
cell references. Here is the revised formula for cell C5:
=VLOOKUP(B5,Products!$B$2:$C$6,2,FALSE)
After the unnecessary sheet names are removed, the list can be safely
sorted, and the correct results will show for each item.
Download the Sample Files
1. Get the
(Excel 2007 and later). The zipped file is in xlsx format, and does not contain any macros.
2. For Excel 2003, get this version of the
The zipped file is in xls format, and does not contain any macros.
3. To see the problem that can occur when sorting with VLOOKUP, get this . The zipped file is in xlsx format, and does not contain any macros.
More Tutorials
More Function Tutorials
Copyright & Contextures Inc. 2017
Last updated: August 30,社会化媒体
了解更多>>
桂ICP备 号
桂公网安备 36号
阅读下一篇
自媒体运营攻略
行业经验交流
Hi，在你登录以后，就可以永久免费的收藏任何您感兴趣的内容，关注感兴趣的作者！
已有帐号请点击
我们刚刚给你发送了一封验证邮件
请在48小时内查收邮件,并按照提示验证邮箱
感谢你对微口网的信任与支持
如果你没有收到邮件，请留意垃圾箱 或 重新发送
还没有帐号请点击
又想起来了？
我们刚刚给你发送了一封邮件
请在5分钟内查收邮件,并按照提示重置密码
感谢你对微口网的信任与支持
如果你没有收到邮件，请留意垃圾箱 或 重新发送
如果你没有收到邮件，请留意垃圾箱 或
意见与建议
请留下您的联系方式
* 留下您正确的联系方式，以便工作人员尽快与你取得联系
转藏至我的藏点您好，欢迎来Office 求助！
一键分享：
=VLOOKUP(B3,IF({1,0},G$3:G$5,F$3:F$5),2,0)公式中的IF(
=VLOOKUP(B3,IF({1,0},G$3:G$5,F$3:F$5),2,0)公式中的IF({1,0},G$3:G$15,F$3:F$15)作何解释？
离问题结束还有：-1721天-15小时回答数：1
网友的回答共1条
我的理解:{1,0}的含义是1代表TRUE(即逻辑值为真)，0代表FALSE(即逻辑值为假)，公式为=VLOOKUP(B3,IF({TRUE,FALSE},G3:G17,F3:F17),2,0)也同样正确，这样更好理解.通过执行IF({TRUE,FALSE},G3:G17,F3:F17)为真，得到G3:G17这列数，由于是数组，再执行FALSE得到F3:F17，因此得到一个2列多行的数组.
相关已解决问题
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
·&1个回答
网友正在问
更多ET表格/EXCEL相关知识