If Statement with Two Array Results

[JasmineL]

Is it possible to use an array around two different index/match calculations?

The formula below does return my data when cell $N$4=2018, but when cell $N$4=2019, I receive #NUM errors.

{=(IF($N$4=2018,(INDEX('[Count Master 2018.xlsx]MASTER'!$A$3:$A$1000,SMALL(IF($N$3='[Count Master 2018.xlsx]MASTER'!$Q$3:$Q$1000,ROW('[Count Master 2018.xlsx]MASTER'!$A$3:$A$1000)-ROW('[Count Master 2018.xlsx]MASTER'!$A$3)+1),ROW(1:1)))),(INDEX('[Count Master 2019.xlsx]MASTER'!$A$3:$A$1000,SMALL(IF($N$3='[Count Master 2019.xlsx]MASTER'!$Q$3:$Q$1000,ROW('[Count Master 2019.xlsx]MASTER'!$A$3:$A$1000)-ROW('[Count Master 2019.xlsx]MASTER'!$A$3)+1),ROW(1:1))))))}

However, if I remove the second set of indexing and replace with a cell or just text, when cell $N$4 equals 2019, I get the contents of the cell or text.

{=(IF($N$4=2018,(INDEX('[Count Master 2018.xlsx]MASTER'!$A$3:$A$1000,SMALL(IF($N$3='[Count Master 2018.xlsx]MASTER'!$Q$3:$Q$1000,ROW('[Count Master 2018.xlsx]MASTER'!$A$3:$A$1000)-ROW('[Count Master 2018.xlsx]MASTER'!$A$3)+1),ROW(1:1)))),"2019"))}

Result = "2019"

I was hoping it was as simple as I'm missing some parentheses.

Anyone have any input?

Thanks.

 

[StephenCrump]

Take a look at the range: [Count Master 2019.xlsx]MASTER'!$Q$3:$Q$1000

You should one or more #NUM errors there. That will cause your first formula to return #NUM .

By the way, I'm curious why you're using ROW(1:1)? You won't be able to copy this formula down to find the 2nd, 3rd, 4th etc occurrences.

And if you only want the first occurrence, you could use a much simpler INDEX/MATCH formula.

 

[JasmineL]

Thank you for the feedback, Stephen. The formula works just as I need it until I add the 2019 reference. Essentially, we are telling excel to look at a date and then return only those rows with that date from the Count Master. Since we may have someone still on the 2018 Count Master or someone new on the 2019 Count Master, I wanted a rule that pulls the names from 2018 or 2019 based on the year in cell N4.

We could have multiple people being pulled in, but only trying to match to the one date field. When I did just an index/match, it only returns for me the first instance, not all the instances in which cell N4 matches. Is there a better way?

 

[StephenCrump]

Did you find the #NUM error?

Your first formula should work fine. If it's returning #NUM it means either there is a #NUM error (or errors) somewhere (i.e. anywhere) in the range [Count Master 2019.xlsx]MASTER'!$Q$3:$Q$1000, or in the specific cell that you are returning from the range [Count Master 2019.xlsx]MASTER'!$A$3:$A$1000.

Sorry, ignore my comment about Row(1:1) not incrementing for the 2nd, 3rd, 4th etc occurrence. I responded too quickly and muddled ROW with ROWS.

But I would prefer to use ROWS as my counter for the SMALL function, e.g. if the first formula was in cell C10, say, then I'd use ROWS(C$10:C10) and copy down. This provides better protection against the user adding or deleting rows toward the top of the worksheet, and hence impacting your ROW(1:1), ROW(2:2) etc.

 

[JasmineL]

Thankfully, I did. I'm happy to report it wasn't my formula, it was my indexed range. The range contained #N/A and #NUM errors. Once I narrowed my range to only populated cells, the formulas worked. Thanks a bunch! I'll have to check out ROWS. Thanks again.