Excel - Worksheet Value Lookup Based On ID [closed

问题:

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

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

Thanks!

回答1:

You need to use the array formula for this.

And the formula is: =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;""))

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