How to get Index to search two different Columns

[LightHeart7]

I have been using Index with small to pull out multiple lines from searching. however I am stuck. I have Dates in two columns I want to search from. Column A which is Starting Date and Column J which is End. I'm search purely by month. I want it to pull any reference with that month. So if
5/1/13|A|B|1|6/1/13
5/1/13|A|B|2|7/13/13
6/1/13|A|B|3|8/4/13
6/1/13|A|B|4|6/16/13

So if i wanted the month of june these to show up
5/1/13|A|B|1|6/1/13
6/1/13|A|B|3|8/4/13
6/1/13|A|B|4|6/16/13

Is this possible?

[ben_hensel]

I don't understand:
1) The layout of your sample data (what are those ||| things doing?),
2) How INDEX(array, SMALL(other?_array, kth) ) works.
3) How you want the output to display.

Generally I think this will do what you want?:

create a dummy column in K.
K1 = OR( Month(A1) = 6, Month(J1) = 6)
pull down
Then filter column K for TRUE.

[LightHeart7]

Sorry that was supposed to represent columns | | But i have a combobox that drops down showing each month. This is what I have just for one column month.
Index(First, Small(IF(Date(,month(A1),)=Date(IF(Date(,Month(A3:A500),)Row(A3:A500)),Row(A1)

AND/OR didn't seemed to work with me in this but maybe I placed it wrong?

[ben_hensel]

can you maybe post an example spreadsheet?

You've got at least two syntax errors in the INDEX() - a missing right paren and a missing comma, I think. AND it looks like it's an array formula? But even then I can't tell what your FALSE is trying to do?



Also: row 2 in your example, starts in May and ends in August. Does that count as "something in June"?

In that case you might want something like,
K1 = AND( Month(A1) >= 6, 6 <= Month(J1) )

[ChemistB]

Arrayed functions don't like AND and OR. Instead use + for OR and * for AND. See my example attached. I am returning column F if the month in A or E is June. The formula I used is

Please Login or Register  to view this content.

Does that help?

[LightHeart7]

Good to know! Thanks! I struggled for hours with that And and Or in that. Thank you so much!