Find values on based on multiple criteria

[johnlol]

Hi,

I was if someone could help.

I have attached an example of spreadsheet. I want to be able to show, on a given date, just the cities that have not been complete on another sheet. Is this possible? The spreadsheet I will be using will have a lot more data than this example.

Any help would be much appreciated.

Thanks,

John

[Glenn Kennedy]

Hi. Please amend your profile to show the Excel version you are using. It is clearly later than 2003....

If it's 2010 or later:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,AGGREGATE(14,6,ROW(Sheet1!$A$2:$A$7)/(INDEX(Sheet1!$B$2:$H$7,,MATCH(Sheet2!$A$2,Sheet1!$B$1:$H$1,0))="Not complete"),ROWS($1:1))),"")

copied down.

[johnlol]

Thanks Glenn.

I’ve updated my profile.

It’s showing Manchester where it should be Liverpool. Is this due to the rows function?

[bebo021999]

I thought just slightly amenmend:

=IFERROR(INDEX(Sheet1!$A$2:$A$7

to be

=IFERROR(INDEX(Sheet1!$A:$A

[Glenn Kennedy]

Yep. Silly mistake by yours truly...

[johnlol]

Thanks bebo.

It’s working on that date but not when there is more than one ‘not completed’ in a day?

[Glenn Kennedy]

God, I am out of practice!!

See now. It's OK.

[johnlol]

Thanks. It’s working on that example but, when I try to replicate it, the first value is showing but it’s showing 0 values for any others?

[johnlol]

Sorry ignore that last post. It’s seemed to have worked. Thanks for your help.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.