Excel Formula to Look Up a Percentile by Weight Range and Gender
Question details
Return a percentile from a lookup table based on a person's weight and gender without using a macro.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has weight and gender inputs and separate weight thresholds for female and male percentile ranges.
- Observed behavior
- The desired formula should select the appropriate gender-specific weight range and return its percentile.
Check that the weight inputs and thresholds use the same units and that each threshold represents the lower bound of a range. For the example below, arrange both threshold columns in ascending order, with the corresponding percentile in the same row.
Use INDEX and MATCH with gender-specific weight thresholds
Use this formula to return the percentile associated with the largest threshold that does not exceed the entered weight.
Enter ascending female weight thresholds in N2:N22, ascending male thresholds in O2:O22, and their corresponding percentile values in P2:P22. Each row must represent the same percentile for both threshold columns.
Enter the numeric weight in F2 and F or M in H2.
In the result cell, enter =IF(H2="F",IFERROR(INDEX($P$2:$P$22,MATCH(F2,$N$2:$N$22,1)),""),IF(H2="M",IFERROR(INDEX($P$2:$P$22,MATCH(F2,$O$2:$O$22,1)),""),""))
Test the formula with weights at and between known thresholds, then drag the result cell's fill handle down to apply it to other rows.

Look Up Weight Percentiles in WPS Spreadsheet
Use the same INDEX and MATCH approach in WPS Spreadsheet to select a gender-specific weight threshold and return its percentile without a macro.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook, or create a new spreadsheet.
- 2. Arrange the table: Enter ascending female thresholds in N2:N22, male thresholds in O2:O22, and corresponding percentile values in P2:P22.
- 3. Enter the inputs: Type the weight in F2 and F or M in H2.
- 4. Enter the formula: Select a result cell and enter =IF(H2="F",IFERROR(INDEX($P$2:$P$22,MATCH(F2,$N$2:$N$22,1)),""),IF(H2="M",IFERROR(INDEX($P$2:$P$22,MATCH(F2,$O$2:$O$22,1)),""),""))
- 5. Test and save: Check the result against known thresholds, then use File > Save As to save the workbook in XLSX format.

Frequently Asked Questions
Why does the formula return a blank?
Check that H2 contains F or M and that F2 is a number at or above the first threshold in the matching gender column. The IFERROR function also hides lookup errors, so verify the table ranges and values.
Does MATCH return the percentile itself?
No. MATCH returns the position of the matching threshold. INDEX uses that position to return the corresponding percentile from P2:P22.
What happens when the weight falls between two thresholds?
MATCH with a third argument of 1 selects the largest threshold less than or equal to the weight. The threshold column must be sorted in ascending order.
How do I display the result as a percentage?
If the percentile values in column P are stored as decimals, such as 0.75 for 75%, select the result cells and choose Percentage from the number format options on the Home tab.
Can the lookup table be on another sheet?
Yes. Update each formula range to include the sheet name, such as Lookup!$N$2:$N$22, Lookup!$O$2:$O$22, and Lookup!$P$2:$P$22.




