#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Lookup Ranges

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default Lookup Ranges

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

"Sr Accountant" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Lookup Ranges

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" wrote:

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

"Sr Accountant" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default Lookup Ranges

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" wrote:

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" wrote:

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

"Sr Accountant" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Lookup Ranges

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

"Eduardo" wrote:

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" wrote:

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" wrote:

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

"Sr Accountant" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default Lookup Ranges

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

"Eduardo" wrote:

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" wrote:

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" wrote:

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

"Sr Accountant" wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Lookup Ranges

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

"Eduardo" wrote:

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

"Eduardo" wrote:

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" wrote:

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" wrote:

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

"Sr Accountant" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lookup on values between ranges The cat Excel Discussion (Misc queries) 8 April 22nd 09 03:00 PM
Lookup one value and count in two ranges Leanne at Work Excel Discussion (Misc queries) 4 July 30th 08 12:33 PM
Name ranges and Lookup eionjess Excel Worksheet Functions 3 January 26th 07 02:45 AM
Lookup Value between 2 ranges Dominique Feteau Excel Worksheet Functions 2 November 1st 05 06:17 PM
Ranges with in a LOOKUP Elijah Excel Worksheet Functions 2 November 23rd 04 10:40 AM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"