# Not sinking in - Lookup

#### SteveWebb

##### Board Regular
Book1
ABCDEFGHI
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.

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

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

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

Just tried it, excellent

Thank you. will now take time to understand it.

Best wishes
Steve

Hi SteveWebb:

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))

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.

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!

Replies
3
Views
196
Replies
5
Views
180
Replies
2
Views
566
Replies
1
Views
840
Replies
2
Views
682

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.

### Which adblocker are you using?

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

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