Sorting data with multiple conditions.

[ck123]

Hi everyone.

I'm working on making a calander in excel that can sort data to show when a project will take place, and I feel as if I've gone about this the wrong way as it's getting complicated and it's not a flexible method.

An example of the sorting code is =IF(AND($F$3=E$14,$E$3=E15,$C$3="North"),$B$3,IF(AND($F$4=E$14,$E$4=E15,$C$4="North"),$B$4,IF(AND($F$5=E$14,$E$5=E15,$C$5="North"),$B$5,IF(AND($F$6=E$14,$E$6=E15,$C$6="North"),$B$6,IF(AND($F$7=E$14,$E$7=E15,$C$7="North"),$B$7,IF(AND($F$8=E$14,$E$8=E15,$C$4="North"),$B$8,IF(AND($F$9=E$14,$E$9=E15,$C$9="North"),$B$9,IF(AND($F$10=E$14,$E$10=E15,$C$10="North"),$B$10,IF(AND($F$11=E$14,$E$11=E15,$C$11="North"),$B$11,"")))))))))

Even though this seems to work, it can only show 1 result, but I have situations where there are more than 1 scenarios meeting the condtions. I've attached the file so this can make sense.

Many thanks

Project Calander.xlsx

[ramananhrm]

Im hoping that you have Excel 2007.

Please try this function

In F19 (Across F19 to X22)

=IFERROR(LOOKUP(2^15,1/(($C$3:$C$11=$D19)*($E$3:$E$11=F$15)*($F$3:$F$11=F$14)),$B$3:$B$11),"")

or

=IFERROR(INDEX($B$3:$B$11,MATCH(1,INDEX((($C$3:$C$11=$D19)*($E$3:$E$11=F$15)*($F$3:$F$11=F$14)),0),0)),"")

[ck123]

Hi, thanks.

That seems to produce the same result as in my previous code. If you play around with the input data table to create the situation where there are two projects in the same location, year and quater it only shows one result.

[ben_hensel]

I feel like, if you already have the data nicely tabulated, trying to grind it into a calender like this is the wrong approach.

I would push towards a pivot table--see attached.