Index and Match (stumped)

[Jek61]

Hi Folks,

I hope someone can assist me with an issue which has stumpted me.

I have a table which consists of 4 Columns Dept A - Dept D and 5 Rows of Customers. Each department ranks the customers 1 - 5. I have a Column with the ranking 1 - 5 and a data validation cell which contains the Departments. I want to display the Customers in Rank order based on the selection in the data validation cell.

I have tried a number of index and match constructs but nothing seems to get the correct result.

I would be very grateful for any assistance.

	Dept A	Dept B	Dept C	Dept E		Ranking	Dept A
Cust A	1​	3​	2​			1​	Cust A
Cust B		2​		1​		2​	Cust C
Cust C	2​	1​		2​		3​	Cust F
Cust D			1​	3​
Cust F	3​		3​

 

[hajiali]

try

=INDEX($B$3:$E$7,MATCH(H3,$A$3:$A$7,0),MATCH($H$2,$B$2:$E$2,0))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1		Dept A	Dept B	Dept C	Dept E		Ranking	Dept B
2	Cust A	1	3	2			1	Cust C
3	Cust B		2		1		2	Cust B
4	Cust C	2	1		2		3	Cust A
5	Cust D			1	3
6	Cust F	3		3
7
Master

Cell Formulas
Range	Formula
H2:H4	H2	=INDEX(SORT(FILTER(A2:E6,FILTER(B2:E6,B1:E1=H1)<>""),MATCH(H1,A1:E1,0)),,1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=$B$1:$E$1

 

[Jek61]

=INDEX($B$3:$E$7,MATCH(H3,$A$3:$A$7,0),MATCH($H$2,$B$2:$E$2,0))

Sorry didn't work just getting and N/A, I did change the ranges to match my sheet. For clarity Cust A is in A4.

Thanks

 

[Jek61]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1		Dept A	Dept B	Dept C	Dept E		Ranking	Dept B
2	Cust A	1	3	2			1	Cust C
3	Cust B		2		1		2	Cust B
4	Cust C	2	1		2		3	Cust A
5	Cust D			1	3
6	Cust F	3		3
7
Master

Cell Formulas
Range	Formula
H2:H4	H2	=INDEX(SORT(FILTER(A2:E6,FILTER(B2:E6,B1:E1=H1)<>""),MATCH(H1,A1:E1,0)),,1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=$B$1:$E$1

Many thanks,

unfortunately I can't use the sort or filter function.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1		Dept A	Dept B	Dept C	Dept E		Ranking	Dept B
2	Cust A	1	3	2			1	Cust C
3	Cust B		2		1		2	Cust B
4	Cust C	2	1		2		3	Cust A
5	Cust D			1	3
6	Cust F	3		3
7
Master

Cell Formulas
Range	Formula
H2:H4	H2	=INDEX($A$2:$A$6,MATCH(G2,INDEX($B$2:$E$6,,MATCH($H$1,$B$1:$E$1,0)),0))

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=$B$1:$E$1

 

[Jek61]

=INDEX($A$2:$A$6,MATCH(G2,INDEX($B$2:$E$6,,MATCH($H$1,$B$1:$E$1,0)),0))

H Fluff,

many thanks this works perfectly.

Have a great day.

John,

 

[Fluff]

Glad we could help & thanks for the feedback.