Not sinking in - Lookup

[SteveWebb]

Book1
	A	B	C	D	E	F	G	H	I
1	name	SKILLA	SKILLB	SKILLC	SKILLD	SKILLE	SKILLF
2	AAA	1	4	2	1	1	1
3	BBB	5	5	3	5	5	5
4	CCC	2	2	1	2	2	2
5	DDD	4	1	4	4	4	4
6	EEE	3	3	6	3	3	3
7	FFF	7	8	7	7	7	7	Skill	SKILLC
8	GGG	6	6	5	6	6	6	RANK	2
9	HHH	9	9	9	9	9	9	Name	#N/A
10	III	8	7	8	8	8	8
Sheet1

What am I doing wrong?
B1-G1 is named range'skill'
I7 is a drop down list for user to select Skill,
I8 is for user (me) to select skill level ie 2
When these criteria are met I9 should give me a name!!
not #N/A.
If somebody can educate me into my error/s, much appreciation from me.

Thanks
Steve

 

[Andrew Poulsom]

Try:

=INDEX($A$2:$A$10,MATCH($I$8,OFFSET($A$2:$A$10,0,MATCH($I$7,Skill,0))))

It's a noemal (non-array) formula.

 

[SteveWebb]

Andrew,
Thank you

However, when I enter Skill A, Rank (best at job) 2 it gives me the name AAA and not CCC as I would expect?

Can you see what I have not done right??

Thanks for the info
Steve,
The hard part is for me to look at and understand the formula

 

[Aladin Akyurek]

With non-volatile INDEX, using your exhibit...

=INDEX(A2:A10,MATCH(I8,INDEX(B2:G10,0,MATCH(I7,B1:G1,0)),0))

 

[SteveWebb]

Aladin,
Just tried it, excellent

Thank you. will now take time to understand it.

Best wishes
Steve

 

[Yogi Anand]

Hi SteveWebb:

In addition to the fine solution provided by Aladin,

You may also try Andrew's solution by changing from

=INDEX($A$2:$A$10,MATCH($I$8,OFFSET($A$2:$A$10,0,MATCH($I$7,Skill,0))))

to

=INDEX($A$2:$A$10,MATCH($I$8,OFFSET($A$2:$A$10,0,MATCH($I$7,Skill,0)),0))

 

[SteveWebb]

Yogi,
Will do, am I right in thinking the '0' hits the first instance of the lookup?

Many thanks
Steve

I am still amazed that such busy people take time out to help others, what a wonderful quality.

 

[Yogi Anand]

Yogi,
Will do, am I right in thinking the '0' hits the first instance of the lookup?
....

Hi SteveWebb:

Yes the 0 reports back the first instance of the exact match!