数组以查找多个列并获取其他列信息 [英] Array to lookup multiple columns and take another columns information

问题描述

我正在尝试在单独的表数组中查找部件号，同一值可能在多行中.该公式应在另一个表中找到该值的每个实例，移至日期列，并确定该值存在于哪个月份中以及它属于哪一行.整个表B3:F15应该是自动化的.我已经附上一张图片，其中尽可能地解释了我要完成的工作.

I am trying to lookup a part number in a separate table array, the same value could be in multiple rows. The formula should find each instance of the value in the other table, move to a date column and determine which months the value exists in and which row it belongs to. The entire table B3:F15 should be automated. I have attached an image with proper explanation of what I am trying to accomplish if at all possible.

公式将输入到B3:F15中.这些是数据将被放入的单元格.每行将在A列中搜索其相应的部件号.即B3至F3仅在查找A3数据.这些行用于确定L2:P4中的部件号是否与它们在列A中的行值匹配，以及是否在属于月份B2:F2的月份中使用.如果在相应月份使用了部件号，则它将从列I输出工厂编号.在某些情况下，L2:P4中的部件号可以存在于多行中，但它们对于每一列都是唯一的.

The formulas would be entered into B3:F15. These are the cells where the data will get pulled into. Each row will search for its corresponding Part Number in column A. I.e, B3 to F3 is only looking for A3 data. The rows are used to determine if a Part number in L2:P4 matches their row value in Column A and if was used in that month belonging to months B2:F2. If the Part number was used in the corresponding month then it will output the Plant # from Column I. In some cases the Part numbers in L2:P4 can exist in multiple rows, but they are unique to each column.

作为示例，我手动填写了PN1001和PN1021的数据.对于PN1001，它属于B3:F3. B3:F3中的每个单元格都将搜索数组L2:P4并意识到只能在M4中找到一次，因此它将查看其行J4和K4中的日期，并确定其使用的月份.在1月，2月和3月使用，它将把Plant#输出到D3，E3，F3.在PN1021的示例中，它是相同的，但是在这种情况下PN1021存在于L2和L4中，因此它将采用J2，K2和J4，K4的日期.

As an example I have manually filled in data for PN1001 and PN1021. For PN1001, this belongs to B3:F3. Each cell in B3:F3 will search the array L2:P4 and realize it is found only once and in M4, so it will look at the dates from its row, J4 and K4, and determine which months it was used in. Since it was used in Jan, Feb, and Mar, it will output the Plant # into D3, E3, F3. In the example of PN1021 it is the same but in this case PN1021 exists in L2 and L4, so it will take the date of J2,K2, and J4,K4.

有些零件号可以是字母和数字的组合，有些可能只是数字.

Some Part numbers can be a combination of letters and numbers, some may just be numbers.

关于如何构建公式，我不确定应该使用索引/匹配/查找的哪种组合.我的理论是B3:F15中的每个单元格都将包含= MATCH(Lookup(columnA with L3:P4))的公式(但是如何获取它以查找多个实例?)查找多个实例并指向行#.

In terms of how to build the formula I am not sure what combination of Index/Match/Lookup I should be using. My theory is that each cell in B3:F15 would include a formula that be =MATCH(Lookup(columnA with L3:P4)) (but how do you get it to lookup multiple instances??) Some research lead me to use this concept to find multiple instances and point to the row #.

SMALL(IF(查找范围=查找值，行(查找范围)，行()-查找范围的起始行以下的行数)

SMALL(IF(Lookup Range = Lookup Value, Row(Lookup Range),Row ()-# of rows below start row of Lookup Range)

一旦找到每个实例，就对行进行索引并比较B3中的单元格是否大于J3且小于K3，以确定应该输入值的月份.如果是，则从行号输出第I列.

Once I can find each instance, then Index the row and compare if the cell in B3 is greater than J3 and less than K3 to determine the months which should have a value entered. IFYES, then output column I from the row number.

如果您有更好的解决方案或想法来执行此操作，请提供反馈.预先感谢您的支持.

If you have any better solutions or ideas to perform this action then please provide feedback. Thanks in advance for the support.

推荐答案

我认为以下公式可以满足您的需求.我发现使用I1:P4数据创建了一个表，并使用了结构化的引用，

I think the formula below will do what you want. I created a table out of your I1:P4 data, and used structured references, as I find it easier to follow.

B3: =IFERROR(
   INDEX(partsTbl,
           MAX(
                    ($A3=partsTbl)*
                      (B$2>DATE(YEAR(partsTbl[[Test Start Date]:[Test Start Date]]),MONTH(partsTbl[[Test Start Date]:[Test Start Date]]),0))*
                      (B$2<DATE(YEAR(partsTbl[[Test Finish Date]:[Test Finish Date]]),MONTH(partsTbl[[Test Finish Date]:[Test Finish Date]])+1,1))*
                      ROW(partsTbl)-ROW(partsTbl[#Headers])),
                      1),
  "")

• $A3=partsTbl返回一个类似于{FALSE,FALSE,TRUE,FALSE;FALSE,FALSE, …}的2D数组，以在表中找到零件编号.
• 由于您在partsTbl中的开始/完成日期并不总是在月初/月末，因此我们需要先将其转换为这样的日期，然后再比较B2:F2中的日期
  • (B$2>DATE(YEAR(partsTbl[[Test Start Date]:[Test Start Date]]),MONTH(partsTbl[[Test Start Date]:[Test Start Date]]),0))
  • $A3=partsTbl returns a 2D array like {FALSE,FALSE,TRUE,FALSE;FALSE,FALSE, …} to find the part number in the table
  • Since your start/finish dates in the partsTbl are not always at the beginning/end of the month, we need to convert them to such before we compare the dates in B2:F2
    • (B$2>DATE(YEAR(partsTbl[[Test Start Date]:[Test Start Date]]),MONTH(partsTbl[[Test Start Date]:[Test Start Date]]),0))

    这篇关于数组以查找多个列并获取其他列信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！