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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Feb 20, 2002
Messages
73
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Feb 20, 2002
Messages
73

ADVERTISEMENT

Aladin,
Just tried it, excellent

Thank you. will now take time to understand it.

Best wishes
Steve
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Board Regular
Joined
Feb 20, 2002
Messages
73
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
Joined
Mar 12, 2002
Messages
11,454
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

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
Top