为什么这样工作？探索VLOOKUP公式 [英] Why does this work? Exploring the VLOOKUP formula

问题描述

我有一个具有4列的excel 2010电子表格。

I have an excel 2010 spreadsheet with 4 columns.

列A：我销售的产品的UPC代码列表。大约300行。

Column A: A list of UPC codes for products I sell. Around 300 lines.

列B：公式（稍后再说）

Column B: Formula (more on this later)

列C：另一个列表UPC代码。这些UPC代码约为10,000行。

Column C: Another list of UPC codes. These UPC codes are around 10,000 lines.

列D：与C列中的UPC代码对应的库存盘点。

Column D: An inventory count which corresponds to UPC codes in Column C.

公式：

=VLOOKUP(A2,C:D,2,FALSE)

想法是将UPC代码与供应商的UPC代码相匹配，以检索相应的库存盘点。

The idea is to match up my UPC codes with my supplier's UPC codes to retrieve the corresponding inventory count.

所有数据已从其他电子表格中粘贴到新的电子表格中，并仅以值粘贴，以确保不导入其他字符或格式。

All data was pasted into a new spreadsheet from other spreadsheets and were pasted as values only to ensure no other characters or formatting was imported.

该公式被拖曳到所有300行。

This formula was dragged down for all 300 lines.

我收到列B中的错误＃N / A，直到我插入以下字符列A中的值：

I received the error #N/A in Column B until I inserted the following character before the values in Column A:

'

注意：通过点击单元格并键入'手动插入' 在上面的框中。

Note: the ' was inserted manually by clicking on the cell and typing ' in the box above.

在完成之后，＃N / A wou ld更改为从D列检索的相应库存。

Immediately after this was done, the #N/A would change to the corresponding inventory retrieved from Column D.

我的问题是为什么这样工作？为什么没有'的方法不起作用？有没有办法在列A中的所有值之前快速添加'？注意：CONCATENATE（'，A2）由于某种原因不起作用。

My question is why does this work? Why does the method without the ' not work? Is there a way to quickly add the ' before all values in Column A? Note: CONCATENATE("'",A2) does not work for some reason.

推荐答案

拉斐尔你有它 - 您的号码作为文本存储在其中一个查找表中，另一个作为数字存储。添加一行将修复它，但这是非常可怕的任务。点击绿色箭头也可以修复它，但是优秀的默认方法对于大桌子来说可能真的很慢（并且它也会改变你的源数据，对于我来说，这是一个没有，就像更新数据一样，你必须再来一遍）。

raphael you've got it there - your number is stored as text in one of the lookup tables, and as a number in the other. Adding a ' to each line will fix it, but it's pretty horrible task. Clicking the green arrow can also fix it, but excels default method can be really slow for big tables (And it also changes your source data, which for me is a no-no, as when you update the data, you'll have to do it again). Changing the formatting rarely helps in this situation.

如果表中包含数字，查找值是以文本形式存储的，则可以将查找值乘以1：

If the table contains numbers and lookup value is number-stored-as-text, you can multiply the lookup value by 1:

=VLOOKUP(A2*1,C:D,2,FALSE)

如果表中包含text，查找值是number，则可以将其转换为如下文本：

If the table contains "text" and lookup value is number, you can convert it to text like this:

=VLOOKUP(TEXT(A1,0),C:D,2,FALSE)

这篇关于为什么这样工作？探索VLOOKUP公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！