1

On Sheet1 in column A, I have a work order number, and on Sheet2 in column A, I have a list of invoices -- each referencing one of the work order numbers on Sheet1. There are multiple invoices for each work order. Also on Sheet2 I have the last service date covered for each invoice period.

I need to look up the latest service date invoiced for each work order. I have tried

=MAX(IF($A2=Sheet2!A2,Sheet2!C:C))

with the result always being 1/0/1900.

  • Could you rename your question to better describe the problem you are experiencing with Excel rather than the business problem you are facing? – Kazark Mar 13 '14 at 14:27
2

Try this array formula -

=MAX(IF(A2=Sheet2!$A:$A,Sheet2!$C:$C))

An array formula must be entered with ctrl + shift + enter and show curly brackets {} around it.

0

You were on the right track with what you tried, but you needed to use an array formula to make it work. Paste the following (adjust the range references to match your sheets) in the formula bar and then press Ctrl+Shift+Enter.

=MAX(IF(A1=Sheet2!$A$1:$A$13,Sheet2!$C$1:$C$13,-1))

The Ctrl+Shift+Enter entry enters the formula as an array formula. Array formulas allow you to apply a function to an array of values. In this case, it applies the comparison of A1 to each value in column A on Sheet2. This formula will return an error if no matching invoice number is found.

This formula assumes that the dates on Sheet2 are formatted as Excel dates, i.e., they are a number value represented as a date. If by chance your dates are formatted as Text (not General or a Date format), this formula will not work as intended. In this case, use the formula below entered as an array formula.

=MAX(IF(A1=Sheet2!$A$1:$A$13,DATEVALUE(Sheet2!$C$1:$C$13),-1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.