Excel 查找功能 [英] Excel Vlookup Function

问题描述

我有一个包含两个工作表的电子表格.每个工作表都包含 C 列，我只需要提取破折号后的最后一个数字.因此，在单元格 D 中，它使用以下公式执行此操作:

I have a spreadsheet that contains two worksheets. Each Worksheet contains the C column which I need to only extract the last numbers after the dash. So in Cell D it does this by using the formula:

=TRIM(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",100)),100))  

正确的数字显示在单元格 D 中，如您所见.

and the correct number is shown in Cell D as you can see.

A   B   C                        D  E
250 stevem  GP CONSTRUCCION-9-50399 50399   4/12/2013 0:00
223 stevem  ANIMATIC MEDIA-9-50400  50400   4/12/2013 0:00
224 stevem  DIGITAL ENGINEE-9-50401 50401   4/12/2013 0:00

然后我需要对工作表 1 中的单元格 D 进行 vlookup 并将其与工作表 2 中的单元格 D 匹配，其中包含相同的公式，用于仅提取 -

I then need to do a vlookup on cell D in sheet 1 and match it with Cell D in sheet two which contains the same formula for extracting only the last digits after the -

我的 Vlookup 是:

My Vlookup is:

=VLOOKUP(D:D,Sheet2!A:F,2)

但我认为因为查找单元格不包含 Just 值，而是包含返回值的公式，所以我收到错误 #N/A

but I think because the lookup cell does not contain Just values, but rather a formula that returns a value, I get an error #N/A

Vlookup 对我想要做的事情有用吗?

Will Vlookup work for what I am trying to do?

推荐答案

(#N/A) 返回的 VLookup 表示未找到该值.我的猜测是，问题是您正在从工作表 2 的 A 列的工作表 1 中查找 D 的值.

(#N/A) returned by a VLookup means that the value was not found. My guess is that the issue is you are looking for values of D from Sheet 1 in column A of Sheet 2.

VLookup 函数的参数有[Lookup Value]、[Table Array]、[Column Index Num]、[Range Lookup]

The VLookup function has parameters of [Lookup Value],[Table Array],[Column Index Num],[Range Lookup]

查找值 是您要搜索的值.Table Array 是要搜索的值的范围.第一列是搜索的内容.Column Index Num 是表数组的列号，如果找到Lookup 值则应返回其值.如果表数组中有多个 Lookup 值的实例，则该函数返回第一个实例.范围查找是真还是假，并确定您是只使用完全匹配 -False 还是近似匹配 -True.

Lookup Value is the value you are searching with. Table Array is the range of values to search in. The first column is what is searched. Column Index Num is the column number of the table array whose value should be returned if the Lookup value is found. If there are multiple instances of the Lookup value in the table array then the function returns the first instance. Range Lookup is true or false and determines if you only use and exact match -False, or a near match -True.

您选择的表数组是 Sheet2!A:D 并且您拥有的列索引号是 2.这意味着 vlookup 正在查找 A 列中的值，如果找到将返回 B 列中的值.如果未找到该值，则 vlookup 返回一般错误 (#N/A).如果需要，您可以使用包含在 VLOOKUP() 函数周围的 IFERROR() 函数来捕获它.

The table array you select is Sheet2!A:D and the column index number you have is 2. This means the vlookup is looking for the value in column A and if it is found will return the value in column B. If the value is not found then the vlookup returns a general error (#N/A). If needed you could capture this with the IFERROR() function wrapped around your VLOOKUP() function.

您描述了要在工作表 2 的 D 列中搜索值，但没有说明要返回的值.如果您只想返回数字，则将范围设置为仅一列就足够了.

You describe you want to search for the value in column D of sheet 2 but you don't express what value you want returned. If you want just the number returned then setting the range to only one column will suffice.

示例:=vlookup(D1,Sheets2!D:D,1,False)

Example: =vlookup(D1,Sheets2!D:D,1,False)

此 VLOOKUP() 函数正在第 2 表的 D 列中查找值 D1，如果找到，则返回数字，否则返回 (#N/A) 并执行完全匹配搜索.

This VLOOKUP() function is looking for the value D1 in the column D on Sheet 2 and if it is found it returns the number otherwise it returns (#N/A) and does an exact match search.

这篇关于Excel 查找功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！