Index & Match with Rank

[SteveWebb]

Hello,
Is it feasible to use Rank with Index & Match.

I am trying to achieve:-
Person rank based on skill level for a task
A3:A70 Name of Person
A2:G2 Task
B3:G70 Rank from 1 to 20, 1 being the best. This sheet is named " Skills"

Index & Match would transfer to another sheet named "Admin"via a userform, and will be used to call up any skill shortages for that team from "Skills"


I have looked through this forum and to be honest I am stuck. Some formulas given seem to fit the bill but I really don't understand them fully.

Can I sort within Index & Match as the skills are changeable? or do I sort then Index & Match??

Any help will much appreciated

Thanks
Steve

 

[fairwinds]

Hi,

This will give you the rank within the specified skill:

=RANK(INDEX(B2:E8,MATCH(H2,A2:A8,0),MATCH(H3,B1:E1,0)),INDEX(B2:E8,1,MATCH(H3,B1:E1,0)):INDEX(B2:E8,7,MATCH(H3,B1:E1,0)))

Book1
	A	B	C	D	E	F	G	H
1	Name	skilla	skillb	skillc	skilld
2	aaa	1	6	3	4		Name	ccc
3	bbb	5	3	3	2		Skill	skillb
4	ccc	5	4	3	0		Rank	3
5	ddd	2	4	2	1
6	eee	5	3	3	2
7	fff	9	4	7	6
8	ggg	5	5	3	2
Sheet2

 

[SteveWebb]

Fairwinds,
Thank you.

You have shed some light, I was only using Index-Match-Match, now I see it's possible to drill down deeper with Index/Match.
It works, I will now work out hopefully how to get this into my drop down userform on 'Admin'.

Much appreciated

Steve