# 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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Andrew Poulsom

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
With non-volatile INDEX, using your exhibit...

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

#### SteveWebb

##### Board Regular
Just tried it, excellent

Thank you. will now take time to understand it.

Best wishes
Steve

#### Yogi Anand

##### MrExcel MVP
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))

#### SteveWebb

##### Board Regular
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

##### MrExcel MVP
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
10
Views
185
Replies
3
Views
416
Replies
1
Views
186
Replies
5
Views
242
Replies
0
Views
393

1,171,323
Messages
5,874,996
Members
433,086
Latest member
swdavies

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