CODEWITHSUNDEEP
excelindexingcount
mark_crown
mark_crown

Reputation: 135

Excel: Index Match using criteria, show unique values?

I am trying to create a calendar in excel. I have sheet 1 which contains my calendar template like so:

April 8           April 9           April 10           April 11         April 12

SubjectA          SubjectD          SubjectG           SubjectJ         SubjectK
DescriptionA      DecriptionD       DescriptionG       DescriptionJ     Desc K


SubjectB          SubjectE          SubjectH         
DescriptionB      DescriptionE      DescriptionH


SubjectC          SubjectF          SubjectI
DescriptionC      DescriptionF      DescriptionI

I have rows of data in sheet 2 like so:

ID      Date            Subject      Description
1       08/04/2016      SubjectA     DescrptionA
2       08/04/2016      SubjectB     DescriptionB
3       08/04/2016      SubjectC     DescriptionC
4       09/04/2016      SubjectD     DescriptionD
5       09/04/2016      SubjectE     DescriptionE
6       09/04/2016      SubjectF     DescriptionF
7       10/04/2016      SubjectG     DescriptionG
8       10/04/2016      SubjectH     DescriptionH
9       10/04/2016      SubjectI     DescriptionI
10      11/04/2016      SubjectJ     DescriptionJ
11      12/04/2016      SubjectK     Desc K

Basically i want to use an index match formula in the cells of my calendar which will give me the 'subjects' where the dates match.

For instance on sheet 2 there are 3 rows with subjects 'A','B', and 'C' all with the date 08th April. Therefore these should show up in the calendar on sheet 1 under 08th April.

I am trying to use this formula to do this:

=INDEX(Data!$G$2:$G$5000, MATCH(0, COUNTIF(Calendar!$G$18:$G18, Data!$G$2:$G$5000), 0))

This should pull through my subjects which have a matching date and display unique values. But it's not working, it pulls through just any result without ensuring the dates match, and also doesn't show only unique values but shows the same value again and again.

I think i need to define my date match in this formula somewhere but not sure how, or where?

Please can someone show me where i am going wrong?

Thanks in advance

Upvotes: 3

Views: 444

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

To get multiple solutions you need an array formula with ROW to get the rows for INDEX with SMALL like (assuming dates are in column B and subjects in D)

=INDEX(Data!$D:$D,SMALL(IF(Data!$B$2:$B$5000=Calendar!$G$18,ROW($2:$5000)),xxx))

This is an array formula and needs to be confirmed with ctrl + shift + enter

Simply change the xxx to 1 to get the first match, 2 for the second and so on.
If you still have questions just ask ;)

(Done by phone, may contain errors)

Upvotes: 1

Related Questions