当列不匹配时，Excel Vlookup产生#N/A错误，而是建立索引? [英] Excel Vlookup producing #N/A error when column doesn't match, index instead?

问题描述

我在工作簿中有两张工作表，其中包含来自不同日期的数据提取，以便在所需数据中添加新列.但是，我只需要处理原始数据提取(因为自原始提取以来已添加了新项目，因此可以忽略这些项目).因此工作表1有53,875个订单项，工作表2有54134个订单项.

I have two sheets in a workbook with data pulls from different days to add a new column to the data needed. However, I need to work only with the original data pull (as new items have been added since original pull and those can be ignored). So sheet 1 has 53,875 line items and sheet 2 has 54134 items.

第一列是一个用户ID，因此我已经对该列的两个表进行了排序.

The first column is a user ID, so I have sorted both sheets on that column.

我想做的是从工作表2的L列中获取数据，并将其插入工作表1的空白列中，用于 Sheet1！Ax = Sheet2！Ax .我想出的公式是:

What I would like to do, is take the data from column L on sheet 2, and insert it into a blank column on sheet 1 for Sheet1!Ax = Sheet2!Ax. The formula that I came up with is:

= VLOOKUP(Sheet1！A1，Sheet2！A1:L1,12,0)

然后我将其填充到第一张纸中其余的空白单元格中.工作正常，直到工作表2中的第一个新数据项与工作表1不匹配.之后，每个单元格都出现#N/A错误.

I then filled it in for the rest of the blank cells in the first sheet. It worked fine, until the first new data item in Sheet 2 failed to match Sheet 1. After that, every cell has the #N/A error.

Vlookup 是用于此任务的正确工具，还是其他工具?

Is Vlookup the correct tool to use for this task, or would it be something else?

推荐答案

如果可以的话，请使用表格而不是范围-表格会自动使用新数据进行扩展，绝对范围不会.

If you can, use tables instead of ranges - a table will automatically expand with new data, an absolute range won't.

在源"范围内的任意位置单击，然后从主页功能区(在样式"组下)中选择格式化为表格".然后对您的目标"范围执行相同操作.

Click anywhere in your "source" range, select "Format as Table" from the Home Ribbon (under the "Styles" group). Then do the same for your "target" range.

现在您有两个表，您可以使用表公式，而不必再担心拖拽公式"，因为该公式会自动应用于整个列！

Now that you have two tables, you can use table formulas and never need to worry about "dragging formulas down" ever again, because the formula is automatically applied to the entire column!

假设您在 Table1 和 Table2 中有一个 Key 列，并且正在寻找相应的 Column1 Table2 中的值，用于 Table1 中的每个 Key ；公式可能如下所示:

Say you have a Key column in Table1 and Table2, and you're looking for the corresponding Column1 value in Table2 for each Key in Table1; the formula could look like this:

=VLOOKUP([@Key],Table2[[#All],[Key]:[Column1]],2,FALSE)

如果标题更改，则公式将自动更新.如果将数据添加到 Table1 中，则查找公式将自动添加到新行中.如果在 Table2 中添加了数据，则无需调整公式！

If headings change, the formula updates automatically. If data is added in Table1, the lookup formula is automatically added to the new rows. If data is added in Table2, the formula doesn't need to be adjusted!

爱表，他们会爱你！

这篇关于当列不匹配时，Excel Vlookup产生#N/A错误，而是建立索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！