Return latest non blank cell with multiple criteria

**dluhut** · 03-23-2017, 04:15 PM

Type	ID	Order	Date	Desired Result via Formula
1	Z1	12345	Feb 5, 2016
1	Z2	23456	Mar 8, 2016
1	Z3	34567	Apr 2, 2016	Yes
1	Z4		May 1, 2016
1	Z5		Jun 9, 2016
1	Z6		Jul 3, 2016	Yes
2	Z1	98765	Mar 3, 2016
2	Z2	87654	Apr 7, 2016
2	Z3		May 4, 2016
2	Z4	65432	Jun 8, 2016	Yes
2	Z5		Dec 6, 2016
2	Z6	46578	Dec 9, 2016	Yes

Hello,

The table above is a sample as well as the desired result that I'd like to accomplish.

The login behind it are:

Scenario 1:
Return a 'YES' for the last non-blank 'Order' for the same 'Type'.

Example of Scenario 1:
Given the 'Type' 1, I have 3 non-blank 'Orders' (12345, 23456 and 34567) and 3 blank 'Orders' (date May1, Jun9 and Jul3). Although 'Orders' 12345 and 23456 are not blank, but they're not the 'last non blank cell' from Type 1. Thus invalid. The only valid is 'Orders' 34567.

Given the 'Type' 2, I have 4 non-blank 'Orders' (98765, 87654, 65432 and 46578) and 2 blank 'Orders' (date May4, and Dec6). Although 'Orders' 98765, 87654 and 65432 are not blank, but they're not the 'last non blank cell' from Type 2. Thus invalid. The only valid is 'Orders' 46578.

Scenario 2: ASSUME TODAY() is Aug 31, 2016
Return a 'YES' for the same 'Type' and 'Dates' that are as close as TODAY'S DATE (which is Aug 31, 2016).

Example of Scenario 2:
Given the 'Type' 1, the closest date for Aug 31, 2016 is Jul3.

Given the 'Type' 2, the closest date for Aug 31, 2016 is Jun8.

I hope the above scenarios and examples explained clearly what I'm trying to accomplished. Thus the question is, what functions that enable me to get the result that I want? I tried LOOKUP formula to get the last non-blank cell, but it won't work with multiple criteria.

**Glenn Kennedy** · 03-24-2017, 04:11 AM

Hi there. Here's one way, using this array formula:

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...