Not sinking in - Lookup

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73
:oops:
Book1
ABCDEFGHI
1nameSKILLASKILLBSKILLCSKILLDSKILLESKILLF
2AAA142111
3BBB553555
4CCC221222
5DDD414444
6EEE336333
7FFF787777SkillSKILLC
8GGG665666RANK2
9HHH999999Name#N/A
10III878888
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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 :)
 
Upvote 0
With non-volatile INDEX, using your exhibit...

=INDEX(A2:A10,MATCH(I8,INDEX(B2:G10,0,MATCH(I7,B1:G1,0)),0))
 
Upvote 0
Aladin,
Just tried it, excellent

Thank you. will now take time to understand it.

Best wishes
Steve
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
SteveWebb said:
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!
 
Upvote 0

Forum statistics

Threads
1,218,617
Messages
6,143,498
Members
450,491
Latest member
Ccruz444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top