Lookup Column Title Using 2 Variables

[stockmonkeys]

Howdy, i've been having trouble working out a formula to solve my current problem, hoped you folks could help.

I have one sheet where i have a list of names, including their specialty and their income.

John Doe

Allergy/Immunology

195000

on another sheet is a benchmark which lists specialties and incomes by percentile(like the following)

Specialties	10 % tile	11 % tile	12 % tile
Allergy/Immunology	189,971.17	194,055.58	196,723.75
Anesthesiology	291,422.18	299,998.40	303,869.23

What I would like to do is have a formula that produces an individual's pay percentile (column title). Specialties match exactly, but the incomes don't - (i'm fine with the nearest below or above) and ultimately returning the doc's percentile score - so '11 % tile' or '12 % tile' as the output for john doe.

John Doe

11 % tile

any help/advice you can provide would be appreciated!

Thanks

[JohnTopley]

Can you please post a small sample file (not image) showing expected results.

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[JohnTopley]

Try

=IFERROR(INDEX($B$1:$D$1,,MATCH($C7,OFFSET($A$1,MATCH($B7,$A$2:$A$4,0),1,1,3),1)),"")

Input table in A1 to D4

Doc data in A7 to E7

[JohnTopley]

@Ford, Posted reply before I saw your message re thread title.

[stockmonkeys]

Edited the bulk of the post to clean up & clarify

[JohnTopley]

See post #4

[FDibbins]

Thanks guys, it's all good

[stockmonkeys]

@JohnTopley it looks like your equation works! thanks very much for your help!

[JohnTopley]

If you have a solution could you please mark thread as solved ("Thread Tools" at top of first post).