Help with Eisenhower Matrix xls

[NotASuperGenius]

Good evening friends. I am working on a spreadsheet that includes "initiatives" that have been bucketed into a pseudo-Eisenhower Matrics (Do first, Do next, Do later, Delete). The spreadsheet layout is something like below (Initiatives in column A, Priority in column J and E.Sort in column N). I have managed to use {=IFERROR(INDEX($A$2:$A$185, SMALL(IF(Q$1=$N$2:$N$185, ROW($A$2:$A$185)-1,""), ROW()-1)),"")} do capture the E.Sort. So I have a list of all the "Do first" initiatives. What I would like to do is extend the sort by including "Priority" (High, Medium, Low). So I can display all the "High, Do first" initiatives. My attempts to combine the forumula above with another lookup haven't worked so I have combined A & J in a new formula but there has to be a more elegant way. Your help, as always, is appreciated - Cheers, NASG

Initiative | Priority | E. Sort
-
Margaritas | High | Do first
-
Tacos | Low | Delete
-
Salsa | Medium | Do first

 

[Aladin Akyurek]

Book1
	A	J	N	O	P	Q	R	S	T
1	Initiative	Priority	E. Sort			High
2	Margaritas	High	Do first			Do First
3	Tacos	Low	Delete			Idx	Initiative	Priority	E. Sort
4	Salsa	Medium	Do first			1	Margaritas	High	Do first
5	Tango	High	Do First			4	Tango	High	Do First
6
7
Sheet1

In Q4 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($J$2:$J$185=$Q$1,IF($N$2:$N$185=$Q$2,ROW($A$2:$A$185)-ROW($A$2)+1)),ROWS($Q$4:Q4)),"")

In R4 just enter, copy across, and down:

=IF(Q4="","",INDEX($A$2:$N$185,$Q4,MATCH(R$3,$A$1:$N$1,0)))

 

[NotASuperGenius]

Aladin - thank you. A better solution than what I came up with.

 

[Aladin Akyurek]

Aladin - thank you. A better solution than what I came up with.

Glad to help.

 

[NotASuperGenius]

My apologies for the feature creep but...I've moved "Priority" and "E.sort" into dropdown menus so that I can select "High" and "Do first". Your formula works perfectly. If I wanted to only display all the "High" items or all the "Do first" items could that be done?

 

[Aladin Akyurek]

My apologies for the feature creep but...I've moved "Priority" and "E.sort" into dropdown menus so that I can select "High" and "Do first". Your formula works perfectly. If I wanted to only display all the "High" items or all the "Do first" items could that be done?

Yes. You can leave Q1 or Q2 or both empty.


This flexibility requires us to only change the formula of Q4...

Control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($J$2:$J$185=IF($Q$1="",$J$2:$J$185,$Q$1),IF($N$2:$N$185=IF($Q$2="",$N$2:$N$185,$Q$2),ROW($A$2:$A$185)-ROW($A$2)+1)),ROWS($Q$4:Q4)),"")

 

[NotASuperGenius]

Aladin - super cool. Thank you

 

[Aladin Akyurek]

Aladin - super cool. Thank you

You are welcome.