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.

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.

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.

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.

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.

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.

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.

Lookup Ranges
 

You too

"Sr Accountant" wrote:

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.

Lookup Ranges
 

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

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.

Lookup Ranges
 

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!

"Eduardo" wrote:

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

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.