英文:
Using a specified conditional VLOOKUP for values in a column
问题
我有一个SKU编号、UPC代码和日期的列表。我想要获取特定SKU编号的UPC编号和最后使用日期。由于存在重复项，我在D列中创建了一个序列，标记了SKU编号出现的次数。
我尝试使用VLOOKUP函数填充列，以便根据D列中的序列提取日期和UPC。有没有办法使用条件语句，通过VLOOKUP函数使这个过程更简单？
我尝试使用以下公式：
=IF(['file name.xlsx]filename'!$D:$D=1,VLOOKUP(lookup value, $A:$D,2,0),"")
我逻辑上是这样考虑的：如果D列中的单元格的值等于1，那么在B列和C列中查找UPC或日期，并将其打印在我想要的列中。
在图像中，A列是我开始的数据，但我需要根据第一个图像中的序列填写UPC1、LastUse1等。感谢任何帮助。谢谢！
英文:
I have a list of SKU numbers, UPC codes, and dates. I am trying to get the UPC number and Last Use date for a specific SKU number. Since there are duplicates, I have created a sequence in column D that labels the number of times that SKU number appears.
I am trying to fill columns using a VLOOKUP to specifically pull dates and UPCs for the sequence in column D. Is there a way to use a conditional statement, using a VLOOKUP to make this easier?
I tried using
=IF(["file name.xlsx]filename!$D:$D=1,VLOOKUP(lookup value, $A:$D,2,0),"")
I logically thought about it like this: If the value of a cell in column D = 1, then lookup the UPC or Date in columns B and C and print it in the column I desire.
In the image, column A is what I am starting with, but I need to fill in UPC1, LastUse1, etc. based on the sequence from the first image. I appreciate any help. Thanks!
答案1
得分: 0
最后一个数字是列的数字（所以，在这种情况下，是第一列）
=IF(F2=1,INDEX($C$2:$E$23,MATCH(C2,$C$2:$C$23,0),1))
英文:
The last number is the one of the column (so, in this case, the first)
=IF(F2=1,INDEX($C$2:$E$23,MATCH(C2,$C$2:$C$23,0),1))
答案2
得分: 0
如果我理解正确的话，根据你的Excel版本，以下任一方法都应该适用，而且不需要任何辅助列。
• 在单元格 G2 中使用的公式：
=IFERROR(INDEX($B$2:$C$11,AGGREGATE(15,7,(ROW($A$2:$A$11)-ROW($A$2)+1)/
($F2=$A$2:$A$11),INT((COLUMN(A1)-1)/2)+1),MOD(COLUMNS($A$1:A1)-1,2)+1),"")
上述公式需要向下填充和向右填充。请注意，我已经以一些数据作为示例，你可能需要根据你的情况更改单元格引用或范围。该方法适用于 Excel 2010 及更高版本。
然而，如果你使用的是 MS365，你也可以尝试以下方法。
• 在单元格 F9 中使用的公式：
=LET(u,UNIQUE(A2:A11),HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,
VSTACK(x,TOROW(FILTER(B2:C11,A2:A11=y))))),1)))
英文:
If I have understood correctly, then either of these should work based on your excel versions, also it doesn't needs any helper/auxiliary columns.
• Formula used in cell <kbd>G2</kbd>
=IFERROR(INDEX($B$2:$C$11,AGGREGATE(15,7,(ROW($A$2:$A$11)-ROW($A$2)+1)/
($F2=$A$2:$A$11),INT((COLUMN(A1)-1)/2)+1),MOD(COLUMNS($A$1:A1)-1,2)+1),"")
The above formula needs to be filled down and filled right. Also note I have taken some of the data as an example, you may need to change the cell references or ranges accordingly as per your suit. And works with Excel 2010+ onwards.
However if you are using MS365 you could try this one as well.
• Formula used in cell <kbd>F9</kbd>
=LET(u,UNIQUE(A2:A11),HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,
VSTACK(x,TOROW(FILTER(B2:C11,A2:A11=y))))),1)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论