Finding cell with highest/lowest index from dropdown list?

[mwl_y]

Wasn't too sure how to word this, but here is the situation:
- A table ("Table1") with several columns; focus is on columns 1,2,4
- Column 4 contains a dropdown menu, which uses the list called "Steps", which is defined as G2:G6
- I need to find a given person's most/least progressed step (the "task" doesn't matter)

I know I can use

=UNIQUE(FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13)))

to find the details matching my team and name criteria (and filter out any duplicates), but is there a way to get specifically the value with the lowest index from the "Steps" list? I know INDEX(Steps,[index]) will return the corresponding item on the list, but is there a way to get the opposite info?

See below; the selected area shows what my expected outputs are for what I need.

 

[Cubist]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Team	Name	Task	Current Step			Steps
2	RED	Abby	ABC	Contruction			Contruction
3	RED	Britt	ABC	Contruction			Create a plan
4	RED	Britt	DEF	Create a plan			Gather materials
5	YELLOW	Charles	DEF	Gather materials			Finishing touches
6	YELLOW	Daniel	GHI	Gather materials			Complete
7	ORANGE	Daniel	JKL	Finishing touches
8	YELLOW	Eric	ABC	Finishing touches
9	ORANGE	Eric	DEF	Finishing touches
10	ORANGE	Eric	GHI	Gather materials
11	ORANGE	Felicia	GHI	Contruction
12	GREEN	George	JKL	Contruction			Team	Name	Least progress	Most progress
13	GREEN	Harley	MNO	Complete			BLUE	Julie	Gather materials	Complete
14	BLUE	Julie	PQR	Finishing touches
15	BLUE	Julie	MNO	Complete
16	BLUE	Julie	PQR	Gather materials
Sheet1

Cell Formulas
Range	Formula
I13:J13	I13	=LET(f,FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13)),TAKE(SORTBY(f,XMATCH(f,Steps)),{1,-1}))
Dynamic array formulas.

 

[dreid1011]

See if this does what you need:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team	Name	Task	Current Step			Steps
2	RED	Abigail		Construction			Create a plan
3	RED	Brittany		Construction			Gather materials
4	RED	Brittany		Create a plan			Construction
5	YELLOW	Charles		Gather materials			Finishing touches
6	YELLOW	Daniel		Gather materials			Complete
7	ORANGE	Daniel		Finishing touches
8	YELLOW	Eric		Finishing touches
9	ORANGE	Eric		Gather materials
10	ORANGE	Eric		Construction
11	ORANGE	Felicia		Construction
12	GREEN	George		Complete			Team	Name	Least Progress	Most Progress
13	GREEN	Harley		Finishing touches			BLUE	Julie	Gather materials	Complete
14	GREEN	Ian		Construction
15	BLUE	Julie		Complete
16	BLUE	Julie		Gather materials
Sheet4

Cell Formulas
Range	Formula
I13	I13	=IFERROR(INDEX($G$2:$G$6,MIN(XMATCH(UNIQUE(FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13))),$G$2:$G$6,0))),"")
J13	J13	=IFERROR(INDEX($G$2:$G$6,MAX(XMATCH(UNIQUE(FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13))),$G$2:$G$6,0))),"")

Cells with Data Validation
Cell	Allow	Criteria
D2:D16	List	=$G$2:$G$6

 

[Eric W]

Another way:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team	Name	Task	Current Step			Steps
2	RED	Abigail	ABC	Construction			Create a plan
3	RED	Brittany	ABC	Construction			Gather materials
4	RED	Brittany	DEF	Create a plan			Construction
5	YELLOW	Charles	DEF	Gather materials			Finishing touches
6	YELLOW	Daniel	GHI	Gather materials			Complete
7	ORANGE	Daniel	JKL	Finishing touches
8	YELLOW	Eric	ABC	Finishing touches
9	ORANGE	Eric	DEF	Gather materials
10	ORANGE	Eric	GHI	Construction
11	ORANGE	Felicia	GHI	Construction
12	GREEN	George	JKL	Complete			Team	Name	Least Progress	Most Progress
13	GREEN	Harley	MNO	Finishing touches			BLUE	JULIE	Gather materials	Complete
14	GREEN	Ian	PQR	Construction
15	BLUE	Julie	MNO	Complete
16	BLUE	Julie	PQR	Gather materials
17
Sheet2

Cell Formulas
Range	Formula
I13:J13	I13	=INDEX(G2:G6,AGGREGATE({15,14},6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))
Dynamic array formulas.

 

[mwl_y]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Team	Name	Task	Current Step			Steps
2	RED	Abby	ABC	Contruction			Contruction
3	RED	Britt	ABC	Contruction			Create a plan
4	RED	Britt	DEF	Create a plan			Gather materials
5	YELLOW	Charles	DEF	Gather materials			Finishing touches
6	YELLOW	Daniel	GHI	Gather materials			Complete
7	ORANGE	Daniel	JKL	Finishing touches
8	YELLOW	Eric	ABC	Finishing touches
9	ORANGE	Eric	DEF	Finishing touches
10	ORANGE	Eric	GHI	Gather materials
11	ORANGE	Felicia	GHI	Contruction
12	GREEN	George	JKL	Contruction			Team	Name	Least progress	Most progress
13	GREEN	Harley	MNO	Complete			BLUE	Julie	Gather materials	Complete
14	BLUE	Julie	PQR	Finishing touches
15	BLUE	Julie	MNO	Complete
16	BLUE	Julie	PQR	Gather materials
Sheet1

Cell Formulas
Range	Formula
I13:J13	I13	=LET(f,FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13)),TAKE(SORTBY(f,XMATCH(f,Steps)),{1,-1}))
Dynamic array formulas.

Thanks Cubist! This sort of works, but only if there are only two different stages... if there were a third task for Julie in team Blue, then it would spill over and show three separate stages in order, rather than just the least and most progressed.

Sorry I didn't specify this, but I do need two separate formulas for each of the cells...

 

[mwl_y]

See if this does what you need:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team	Name	Task	Current Step			Steps
2	RED	Abigail		Construction			Create a plan
3	RED	Brittany		Construction			Gather materials
4	RED	Brittany		Create a plan			Construction
5	YELLOW	Charles		Gather materials			Finishing touches
6	YELLOW	Daniel		Gather materials			Complete
7	ORANGE	Daniel		Finishing touches
8	YELLOW	Eric		Finishing touches
9	ORANGE	Eric		Gather materials
10	ORANGE	Eric		Construction
11	ORANGE	Felicia		Construction
12	GREEN	George		Complete			Team	Name	Least Progress	Most Progress
13	GREEN	Harley		Finishing touches			BLUE	Julie	Gather materials	Complete
14	GREEN	Ian		Construction
15	BLUE	Julie		Complete
16	BLUE	Julie		Gather materials
Sheet4

Cell Formulas
Range	Formula
I13	I13	=IFERROR(INDEX($G$2:$G$6,MIN(XMATCH(UNIQUE(FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13))),$G$2:$G$6,0))),"")
J13	J13	=IFERROR(INDEX($G$2:$G$6,MAX(XMATCH(UNIQUE(FILTER(Table1[Current Step],(Table1[Team]=G13)*(Table1[Name]=H13))),$G$2:$G$6,0))),"")

Cells with Data Validation
Cell	Allow	Criteria
D2:D16	List	=$G$2:$G$6

Thanks @dreid1011 ! I think this covers what I need. I'll test it out

 

[Cubist]

Thanks Cubist! This sort of works, but only if there are only two different stages... if there were a third task for Julie in team Blue, then it would spill over and show three separate stages in order, rather than just the least and most progressed.

I don't think my formula is doing that.

Sorry I didn't specify this, but I do need two separate formulas for each of the cells...

I would go with Eric's formula (from an efficiency perspective).

 

[Eric W]

If you want 2 separate formulas, you can just split my formula in 2 pieces by breaking up the {15,14} array like this:

Book1
	G	H	I	J
12	Team	Name	Least Progress	Most Progress
13	BLUE	JULIE	Gather materials	Complete
Sheet2

Cell Formulas
Range	Formula
I13	I13	=INDEX(G2:G6,AGGREGATE(15,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))
J13	J13	=INDEX(G2:G6,AGGREGATE(14,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))

Both versions put the same results in 2 different cells, so functionally they're the same. Also, I intentionally did not use an IFERROR function, because an error in this case would probably denote a problem in the data, which you'd want to know about.

 

[mwl_y]

If you want 2 separate formulas, you can just split my formula in 2 pieces by breaking up the {15,14} array like this:

Book1
	G	H	I	J
12	Team	Name	Least Progress	Most Progress
13	BLUE	JULIE	Gather materials	Complete
Sheet2

Cell Formulas
Range	Formula
I13	I13	=INDEX(G2:G6,AGGREGATE(15,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))
J13	J13	=INDEX(G2:G6,AGGREGATE(14,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))

Both versions put the same results in 2 different cells, so functionally they're the same. Also, I intentionally did not use an IFERROR function, because an error in this case would probably denote a problem in the data, which you'd want to know about.

Thanks @Eric W ! This works great

 

[dreid1011]

If you want 2 separate formulas, you can just split my formula in 2 pieces by breaking up the {15,14} array like this:

Book1
	G	H	I	J
12	Team	Name	Least Progress	Most Progress
13	BLUE	JULIE	Gather materials	Complete
Sheet2

Cell Formulas
Range	Formula
I13	I13	=INDEX(G2:G6,AGGREGATE(15,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))
J13	J13	=INDEX(G2:G6,AGGREGATE(14,6,MATCH(Table1[Current Step],G2:G6,0)/(Table1[Team]=G13)/(Table1[Name]=H13),1))

Both versions put the same results in 2 different cells, so functionally they're the same. Also, I intentionally did not use an IFERROR function, because an error in this case would probably denote a problem in the data, which you'd want to know about.

The reason I used IFERROR was to hide the error when changing the lookup values since INDEX doesn't have a convenient "if not found" option.