Lookup Ranges

[Sr Accountant]

I have a spreadsheet that in Column A has a department and column B has a
wage. I need to look up this wage in a national survey file, that will
return in column C the nat'l percentile, for that department. Is this
possible?

Column A Column B Column C
Acctg $95,243 65th percentile
Mrktg
Nursing

The problme I have ran into with my formula's, is that the survey file will
have of course a range of numbers, not my exact #. Therefore, my vlookup
formula is not working. How can I write a formula that would do this?

Thanks in advance.

 

[Eduardo]

Hi,
when you said national survey do you want to pull an average, could you send
an example of your survey

 

[Sr Accountant]

I am wanting to pull the percentile that my wage falls into. Here is an
example of my survey file.

Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th
%tile....Etc....
Accounting $64,987 $32,765 $33,456

Does this help? I am not sure how to write a forumla to look for the
department name and then the range of what my wage is. I do not need the
median %, I am truly looking for the %tile. If my wages were $33,000 for
Acctg, I would want the formula to return 10th %tile.

Thank you.

 

[Eduardo]

Hi,
Which is the criteria to select 10th in the case you enter a salary of 33000
and no 11th from column D since the salary there is 33456

 

[Sr Accountant]

I entered the 10th %tile, because my wage of $33,000 falls between $32,765 -
$33,456. Is this what you're asking?

 

[Eduardo]

Hi
I am leaving soon try I assumed that the survey is in sheet1 and you are
entering the information in sheet2. in sheet2 Cell C1 enter. Column A
contains the departments and column B you enter the salary

=INDEX(sheet1!b1:G1,AND(MATCH(A1,sheet1!A1:A1000,1),MATCH(B1,Sheet1!B1:E1000,0)))

 

[Sr Accountant]

Thank you. I will try this, and will post a reply if I need further
assistance. Have a great day.

 

[Eduardo]

You too

Thank you. I will try this, and will post a reply if I need further
assistance. Have a great day.

 

[Eduardo]

Hi,
just some clarifications

sheet1 range b1:g1 is the range where you have your %
sheet1 range a1:a1000 is where you have the departments
sheet1 range b2:e1000 is where you have your salaries

change ranges to fit your needs

 

[Sr Accountant]

Thank you. So far I'm not having any luck with it. I need to move on to
other things, so will get back to you.

Thank you again!