excel公式，如果一列中的数字相同，则从另一张工作表中提取值 [英] excel formula to pull values from another sheet if number in one column is the same

问题描述

我正在尝试使发票自动化，并且需要一些帮助.我的发票格式设置在一个选项卡上，所有订单都在单独的选项卡上输入. 订单"标签有多列-发票ID，发票日期，客户ID等.订单中的每个产品都在具有相同发票ID的新行中输入.

I am trying to automate an invoice and need some help. I have my invoice formatted on one tab, and all orders are entered on a separate tab. The 'Orders' tab has multiple columns -- Invoice ID, Invoice Date, Customer ID, etc. Each product within an order is entered on a new row with the same Invoice ID.

我在发票"选项卡上的单元格中设置了公式，以在我仅输入发票ID时从订单"选项卡中获取信息.但是，如何修改公式以使所有产品都具有一个发票ID，才能填充到发票上?下面是我当前的公式:

I have formulas set up in the cells on my 'Invoice' tab to grab the information from the 'Orders' tab when I simply input the Invoice ID. However, how do I modify the formula to get all the products under one Invoice ID to populate on the invoice? Below is my current formula:

= INDEX(订单！E:E，MATCH(发票！$ I $ 10，订单！A:A，0)

=INDEX(Orders!E:E,MATCH(Invoice!$I$10,Orders!A:A,0)

发票ID位于发票"选项卡上的单元格I10中，并且位于订单"选项卡上的A列中. 产品ID位于订单"标签上的E列中.

Invoice ID is in cell I10 on the 'Invoice' tab and column A on the 'Orders' tab. Product ID is in column E on the 'Orders' tab.

该公式在第一种产品上效果很好，但是如何使具有相同发票ID的其他产品出现在发票上第一项下方的行中?

The formula works great on the first product, but how do I get the other products with the same Invoice ID to appear on the invoice in the rows below the first item?

推荐答案

尝试，

=INDEX(Orders!E:E, aggregate(15, 6, row($2:$999)/(Invoice!$I$10=Orders!A$2:A$999), row(1:1)))

这篇关于excel公式，如果一列中的数字相同，则从另一张工作表中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！