CODEWITHSUNDEEP
exceldateindexingmatch
T. Fontanello
T. Fontanello

Reputation: 45

INDEX and MATCH for multiple DATES (Excel)

maybe you can help me on this.

I need a Excel formula to lookup for all multiple events on a same day:

So this:

    DATE    EVENT           SEARCH DATE HERE> 21/01
    21/01     A        
    21/01     B
    21/01     A
    21/01     A
    22/01     A
    22/01     B
    23/01     B

Would become this:

    DATE    EVENT 
    21/01     A
    21/01     B
    21/01     A
    21/01     A

I was trying to use this:

 {=IFERROR(INDEX($A$1:$B$8,SMALL(IF(A2:A8=$F$1,ROW(A2:A8)-1),ROW(A1)),2),"")}

Which works well looking for values, but not looking for dates..

Any idea why? Thank you!

Upvotes: 1

Views: 1996

Answers (2)

Nate
Nate

Reputation: 2519

I got what you asking for simply using:

=IF($A2=$F$1,$A2,"") and =IF($A2=$F$1,$B2,"")

under the date and event columns, respectively in the filtered result list.

Also, in your formula, the "if" only has 2 parameters. Is this intentional?

You can also filter by the following:

Select the date and events cells

enter image description here

Click Format as Table, check My table has headers and hit OK. enter image description here enter image description here

Click on the down arrow next to the column header, and choose Filter. enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Applying a filter would be best but if you want a formula:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,(1/($A:$A=$D$1))*ROW($1:$1040000),ROW(1:1))),"")

Copy over and down as many rows as wanted. The picture the formula was copied down 8 rows.

enter image description here

The Aggregate function is new to Excel 2010 so any that are older than that it will not work.

Upvotes: 1

Related Questions