Excel在另一列中查找列值并在其旁边返回值 [英] Excel Find column value in another column and return value next to it

问题描述

我想对Excel中的数据进行一些操作。作为背景，我在不同的工作表中有2张表格，这些表格带有库存，一个表格用于构造设备所需的材料，另一张表格用于库存的材料，它们两个都具有相同的组件，但排序不同。我想将它们组合如下：
带有所需材料的表需要知道库存价值以验证需要多少，在这种情况下订购它们。

I want to do some manipulation with data in Excel. As a background, I have 2 tables in different sheets with inventory, one is for the material needed to construct a device and the other with the material in stock, both of them have the same components but are sorted different. I want to combine them as follows: The table with the material required needs to know the stock value to verify how many are needed and in that case order them.

例如：

表1表1

Part #    Needed    Stock
1234      5         I want here the value from table 2
5678      5         I want here the value from table 2
9876      6         I want here the value from table 2
5432      7         I want here the value from table 2

表2表2

Part #    Stock
5432      2
9876      4
1234      1
5678      4

重点不是为了将表2中的库存号放入表1的库存栏中而手动检查零件号。

The point is not checking one by one part number manually in order to put the stock number from table 2 in the stock column from table 1.

如果您能帮助我，我将非常感谢。

I will be very thankful if you could help me.

推荐答案

您可以使用INDEX-MATCH功能ns以实现此目标。对于示例中的股票列，您可以将以下内容用作股票列的第2行

You can use the INDEX-MATCH functions to pull this off. For the Stock column in your example you can use the following as the function in row 2 of the stock column

=INDEX(Sheet2!B$2:B$5,MATCH(Sheet1!A2,Sheet2!A$2:A$5,0))

然后，您可以复制公式并向下复制所需的所有行。将公式复制到其他行时，$符号将使您的返回值范围和查找值范围保持稳定。查找值对于随后的每一行都会改变。

You can then copy the formula and copy down for all rows needed. The $ signs will keep your return value range and lookup value range stable as you copy the formula into other rows. The lookup value will change for each subsequent row.

以下是一些注释，目的是描述此处发生的情况。

Here are some notes in order to describe what is happening here.

=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))
Return_value_range – The range that holds the return values
Lookup_value – The value you want to find in the lookup value array
Lookup_value_range – The range containing lookup values
Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

最后，这是一个链接，给出了使用INDEX-MATCH与以前非常常见的VLOOKUP函数 http://eimagine.com/say-goodbye-to-vlookup-和hello-to-index-match /

Lastly here is a link that gives a could description of using the INDEX-MATCH versus a the previously very common VLOOKUP function http://eimagine.com/say-goodbye-to-vlookup-and-hello-to-index-match/

这篇关于Excel在另一列中查找列值并在其旁边返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！