Find Maximum in Column if Corresponding Cell in Another Column is Not a #N/A Error

[Pecan]

Hi,

I am needing help with a formula to determine the latest invoice number based on two columns of information.

Column A - Invoice Number - Simply a sequential list of numbers.

Column B - The "TS (timesheet) Period Ending Date" - contains a VLookup formula that looks up the invoice number in column A. This column also contains #N/A errors that must remain so that the information is not plotted on a chart.

In cell A1, I would like a formula to report the maximum invoice number where column B contains a date (i.e., not a #N/A error). So, in the example below, I want the formula to report a "9" in cell A1.

A B
1 29 Mar 2015
2 INVOICE TS PERIOD
NUMBER ENDING DATE
3 1 9 Nov 2014
4 2 23 Nov 2014
5 3 7 Dec 2014
6 4 28 Dec 2014
7 5 11 Jan 2015
8 6 1 Feb 2015
9 7 15 Feb 2015
10 8 8 Mar 2015
11 9 29 Mar 2015
12 10 #N/A
13 11 #N/A


Any help anyone can offer would be greatly appreciated.

Thanks!

Pam

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,B:B,A:A)