lookup next non-empty cell?

  • Thread starter Thread starter Me!
  • Start date Start date
M

Me!

Hi,

I have a cell (D4) containing a date, and on the next sheet (called NPW)
column A is a sequential list of dates, with column B containing either
nothing or a 1 (an adjacent 1 next to a date indicates this a non-working
day).

What I need to do is:

If the when you vlookup the date in D4 in the NPW!A1:B1000 range and a 1 is
returned, how do I then find the next date in the column A list where the
adjacent B cell is not a 1?

Any help greatly appreciated,

Thanks,

J
 
Try this array formula** :

=IF(SUMIF(NPW!A1:A1000,D4,NPW!B1:B1000),MIN(IF((NPW!A1:A1000>D4)*(NPW!B1:B1000=0),NPW!A1:A1000)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Many thanks.....works a treat. I tried looking at the array product via
'evaluate formula' to see it working but xl just crashes - guess it's too
hard for evaulate to display as 1,0s :-)

Thanks again,

J
__
 
You're welcome. Thanks for the feedback!
xl just crashes
Click to expand...

Yeah, depending on what version of Excel you're using, when you use the menu
command Evaluate Formula certain types of array formulas will cause a crash.

I've "ranted" about this a few times in these groups.
 
Hi,

Instead of using the Evaluate command, evaluate it manually - select a
complete protion of the formula and press F9. After looking at it press Esc
not Enter to return to the formula and continue the evaluation process on
another piece. This is a very useful technique.
 
Hi,

You might try the following array formula:

=MIN(IF((A1:A15>=E5)*(B1:B15="")*ROW(A1:A15)>0,A1:A15,""))

Of course you should adjust for the sheet, and here the date you are looking
up is entered in E5. By array entered I mean you press Shift+Ctrl+Enter to
enter the formula not Enter.
 
select a complete protion of the formula and press F9.
This is a very useful technique.
Click to expand...

But it's also limited in the size of the expression being evaluated. Using
this technique you're bound to get "Formula too long" messages.
 
With the object being to "evaluate the formula itself", all you have to do
(in this case) is drop a couple of 0's from the range.

When using this method I usually try to keep to 10 or 20 cell ranges.
Easier to see a comma or semi-colon in the array.
 
Back
Top