在Excel中使用INDEX和MATCH根据两个条件提取第三个值 [英] Pull a third value on the basis of two criterias using INDEX and MATCH in excel

问题描述

需要一些有关INDEX匹配公式的帮助，..

Needed some help with the INDEX match formula, here goes..

在两张纸上都有出色表现， -数据表中包含各种库存主数据...我们可以看到每个项目都显示为多个批次，并根据它们的存储位置显示各自的数量. -第1张纸是一张订单，我的最终用户希望根据两个标准来获取产品的确切批次.条件是-产品编号和数量要完全匹配.

Have an excel with two sheets, - Data sheet contains an inventory master of sorts where.. we can see each item being displayed with multiple batches in with each of their own quantities depending on where they're stored.. - Sheet 1 is an order form in which my end user would like to get the exact batch of a product on the basis of two criterias.. Criterias being - product number and qty match to fullfill..

Data - Current Inventory

Item Quantity Batch
ABD  10       11223a
ABD  15       24589r
DFG  5        T45678
DFG  67       ghytu8
FGH  10       thnh67
FGH  10       huip78

Sheet 1 - Order form
Item  Quantity  Batch
ABD   8         
DFG   4
DFG   10 
FGH   10

我现在已经在工作表2的批处理字段中尝试了以下用于INDEX/MATCH的公式，但是它似乎不起作用..请告知.

I have now tried the following formula for INDEX/MATCH in the batch field for sheet 2 but it does not seem to work.. please advise..

= INDEX(数据！C12550:R19719，MATCH(1，(数据！C12550:C19719 = Sheet1！A2)*(数据！D12550:D19719 = Sheet1！B2)，0)，7)

=INDEX(Data!C12550:R19719,MATCH(1,(Data!C12550:C19719=Sheet1!A2)*(Data!D12550:D19719=Sheet1!B2),0),7)

请注意，在实际工作表中，工作表1中的批号位于第七列，因此末尾引用的列为7.

Note in the actual sheet the batch numbers in sheet 1 lie on the 7th Column thus the column referenced at the end is 7..

谢谢.

推荐答案

此问题现已解决...我想出了正确的公式，但是由于存在一些循环引用错误，它拒绝在2个不同的工作表之间工作不会消失..

This issue has been resolved now... I figured out the correct formula, however it refused to work between 2 different sheets due to some circular reference error that wasn't going away..

因此，我在同一数据表上单独做了一个部分，并应用了下面的公式，效果很好！

So ive made a separate section on the same data sheet and applied the following formula which works perfectly!

> =INDEX(C:R,MATCH(1,(C:C=W3)*(R:R>=Y3)*(D:D>=X3),0),3)

现在，我可以使用多个条件.

This now has multiple criteria's that I can work with..

这篇关于在Excel中使用INDEX和MATCH根据两个条件提取第三个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！