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;"))

但是您需要将其应用为ARRAY FORMULA，因此当您输入上述公式时: 按下Enter键时，按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键然后按Enter键，它将是一个数组公式，如下所示: {= 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屋！