# Thread: Index Formula with multiple criteria

1. ## Index Formula with multiple criteria

Register for a FREE account, and/
or Log in to avoid these ads!

I have a large database with Items and the corresponding months they are over due for delivery. I would like to Index the list to show items that are overdue between a month range (e/g < 1month, 1-3 months etc).

I have used an array formula to get < 1 month as there is only 1 criteria but I am struggling to add the second criteria to the Array.

I have attached an example of what I am trying to achieve.

All help welcome.

2. You can use an Array* formula like:

=IFERROR(INDEX(\$A\$2:\$A\$7,SMALL(IF(\$B\$2:\$B\$7>=1,IF(\$B\$2:\$B\$7<=3,ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1)),ROWS(F\$2:F2))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down

or you can add a helper column to C2:

=IF(AND(B2>=1,B2<=3),MAX(C\$1:C1)+1,0)

copied down, then in F2:

=IFERROR(INDEX(\$A\$2:\$A\$7,MATCH(ROWS(F\$2:F2),\$C\$2:\$C\$7,0)),"")

copied down.

3. ## Thank You

Thanks NBVC,

Works perfectly. I had something like this from an on-line tutorial but it had additional formula included and I couldn't work out the correct syntax and what to remove.

Appreciate your quick response as I have spend most of the day trying to sort this out. Now I can go to bed!!

How do I mark as solved and what other formalities do I have to take care of to close this?

Mitch

4. no problem... you're good as is.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•