Excel - 基于 ID 的工作表值查找 [英] Excel - Worksheet Value Lookup Based On ID

问题描述

我有两个工作表.第一个列出了我的客户:

I have two worksheets. The first lists my customers:

A          |B            |C
CustomerID |CustomerName |DateLastProject
MC001      |Ron Bolles   |
MC002      |Neal Pearl   |
MC003      |Meg Black    |

在我的第二个工作表上，我列出了为客户完成的项目(带有日期):

On my second worksheet, I list the projects done for the customers (with the date):

A          |B            |C
CustomerID |ProjectID    |Date
MC001      |MP112        |01/03/2013
MC002      |MP113        |01/08/2013
MC003      |MP114        |01/08/2013
MC001      |MP115        |01/15/2013
MC003      |MP129        |02/04/2013

我需要查找每个客户的最后一个项目日期并将其显示在客户工作表的 C 列中.这样客户就会出现:

I need to look up the last project date for each customer and display it in column C of the customers worksheet. So the customers would appear:

A          |B            |C
CustomerID |CustomerName |DateLastProject
MC001      |Ron Bolles   |01/15/2013
MC002      |Neal Pearl   |01/08/2013
MC003      |Meg Black    |02/04/2013

谢谢！

推荐答案

您需要为此使用数组公式.

You need to use the array formula for this.

公式是:=MAX(IF(A2=F2:F6;H2:H6;""))

但是您需要将其应用为数组公式，因此当您输入上述公式时:按下回车键时按 ctrl + shift.公式将显示为:{=MAX(IF(A2=F2:F6;H2:H6;""))}

BUT you will need to apply it as a ARRAY FORMULA, so when you enter the above formula: Push the ctrl + shift as you hit the enter button. And the formula will be shown as: {=MAX(IF(A2=F2:F6;H2:H6;""))}

如果你有两张纸，公式就是

And if you have two sheets, the formula will be

=MAX(IF(Sheet1!A2=Sheet2!A2:A6;Sheet2!C2:C6;""))

(记得输入 F2 并按住 ctrl + shift 然后按回车键，它将是一个数组公式: {=MAX(IF(Sheet1!A2=Sheet2!A2:A6;Sheet2!C2:C6;""))}

(Remember to enter F2 and hold down ctrl + shift and then hit enter button and it will be an array formula as: {=MAX(IF(Sheet1!A2=Sheet2!A2:A6;Sheet2!C2:C6;""))}

这篇关于Excel - 基于 ID 的工作表值查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！