Nested If with Multiple Indexes based on Date Ranges

**jsclark** · 07-01-2013, 01:31 PM

Hello! I hope I get an accurate description out of the gates. I have attached a sample of the worksheet and formula's I am working with.

1) Here is first formula with error:

=IF(AND($Y18>="C",D$4>0),INDEX(LBCensus!$B$1:$ID$25000,MATCH($B18,LBCensus!$B$1:$B$25000,0),MATCH(DATE(YEAR($T18),MONTH($T18)+D$4,DAY($T18)),LBCensus!$B$1:$ID$1,0)),IF(AND($Y18>="N",D$4>0),INDEX(LBCensus!$B$1:$ID$25000,MATCH($B18,LBCensus!$B$1:$B$25000,0),MATCH(DATE(YEAR($T18),MONTH($T18)+D$4,DAY($T18)),LBCensus!$B$1:$ID$1,0)),IF(AND($Y18>="R",D$4>0),INDEX(LBCensus!$B$1:$ID$25000,MATCH($B18,LBCensus!$B$1:$B$25000,0),MATCH(DATE(YEAR($V18),MONTH($V18)+D$4,DAY($V18)),LBCensus!$B$1:$ID$1,0)),IF(AND($U18>0,$V18=0),0,0))))

Located in Col D:Col O of LBR sheet.

Appears to be working until it has to read from Col V rather than Col T.
So, I see it break beginning in Row 14 where it returns an error #N/A.
Row 15 where it is still reading from Col T rather than Col V.
Row 18 where it is returning #N/A (because there are no date ranges to match the Hdate in Col T to the LBCensus range referenced.

Desired result:

Read from Col T wherever Col Y = a value of N or C, but if no valid dates in range result is 0.
Read from Col V wherever Col Y = a value of R.
If Col U has a value >0, return 0 for any dates except those with a value prior to Col U.

Any help that can be provided would be greatly appreciated! Been working on this one for too long!

**Craig K.** · 07-01-2013, 02:46 PM

First off, every letter value you have is ">=C" and will evaluate to TRUE in the first part of your IF() statements. You're coming up on a problem with rows 14 and 18 because, your INDEX-MATCH is looking for a date value in row 1 of the LBCensus tab that doesn't exist. The DAY values in T14 & T18 are 2 and 14 respectively, whereas the LBCensus tab only has DAY values of 1.

Put this in D5 and copy down and over

Formula:

Please Login or Register  to view this content.

This formula will still return an #N/A value for row 14, because you don't have any dates going back to 1989 in the LBCensus tab.

**Craig K.** · 07-01-2013, 02:52 PM

I feel I should point out that I took the portions of your formula out that said DAY($T5) or DAY($V5) and replaced with the value of 1. You can either leave this replacement in, or put those parts back in and fix your date values in column D on the LBCensus tab to only be the 1st of the month.