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

#### Pecan

##### Board Regular
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try...

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

Replies
3
Views
764
Replies
3
Views
434
Replies
6
Views
403
Replies
11
Views
756
Replies
12
Views
893

1,196,355
Messages
6,014,756
Members
441,848
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back