Lookup percentile rank in one sheet based on raw data in another sheet

[garyalan59]

I have normative data, sorted by gender and age, on one worksheet.
I have raw data listed with age and score (females on one worksheet, males on another). I need to lookup the percentile rank of the raw data from the normative data. I need a combination function to do this, but can't seem to locate one. INDEX MATCH MATCH should work, but I'm drawing a blank.

Workbook attached.

Gary Christopher
Oskaloosa, IA

[bebo021999]

In your sample, 1st row, Trad S/R 4th is 35, is it belongs to 40 or 30 tier?
Assumming it is belongs to 40, try:
G2:

Please Login or Register  to view this content.

I2:

Please Login or Register  to view this content.

In case of it is belong to 30 tier, the first MATCH in both formula will be added 1

[garyalan59]

For the first table, the percentile ranks are in Normative Values, column B, and range from 10 to 90.
For a 24 year old female, raw score of 35 should return a percentile value of 70.
Continuing the example, for a 24 year old female,
- raw scores greater than or equal to 40 should return percentile 90
- raw scores less than 40 but greater than or equal to 37 should return percentile 80
- raw scores less than 37 but greater than or equal to 35 should return percentile 70
- raw scores less than 35 but greater than or equal to 33 should return percentile 60
- raw scores less than 33 but greater than or equal to 31 should return percentile 50
- raw scores less than 31 but greater than or equal to 29 should return percentile 40
- raw scores less than 29 but greater than or equal to 26 should return percentile 30
- raw scores less than 26 but greater than or equal to 23 should return percentile 20
- raw scores less than 23 but greater than or equal to 19 should return percentile 10
- raw scores less than 19 should return percentile 0

Similar scheme for Table 3.4

Sorry for insufficient explanation.

Gary

[JeteMc]

Hello Gary and Welcome to Excel Forum.
This proposed solution makes a slight change to the age range cells (in general merged cells hinder further exploration of data). The maximum value of the age range is placed in the cell above the column and the less than symbol (<) is added using custom formatting.
The formula that populates column G is:

Please Login or Register  to view this content.

The formula that populates column I is similar.
Let us know if you have any questions.