Nested IF - Work Around for 7 nest Max

A

AEICHEN

I have consecutive dates in column B and integers in column D. In column B,
there can be anywhere from 0 to 10 dates listed and in column D, there may or
may not be an integer associated with it. If column B is empty, column D
will also be empty.

I need to populate a cell with the date in column B that matches the last
integer in Column D.

For Example, if I have B1:1/1/04, B2:1/2/04, B3:1/3/04, B4:1/4/04 and
D1:900, D2:800, D3:700, D4: Empty. I'm looking for a formula that will
return 1/3/04

Previously, my maximum rows was 5 and I had this formula:
=IF(D5<>0,B5,IF(D4<>0,B4,IF(D3<>0,B3,IF(D2<>0,B2,IF(D1<>0,B1,"n/a")))))

Now I'm being asked to expand this for 10 rows and with the 7 nested
limitation, I'm unable to. Is there a different formula I can use or a way
to get around this?
 
B

Bob Phillips

=INDEX(B1:B100,COUNTA(D1:D100))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top