Function Problems

Excel Formula to Look Up a Percentile by Weight Range and Gender

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Return a percentile from a lookup table based on a person's weight and gender without using a macro.

Look Up a Percentile by Weight Range and Gender in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the lookup table

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.

2
Enter the inputs

Enter the numeric weight in F2 and F or M in H2.

3
Add the lookup formula

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

4
Check and copy the result

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.

Use INDEX and MATCH with gender-specific weight thresholds
Check the range boundaries: Weights below the first threshold return a blank; weights above the last threshold return the final percentile. A blank also appears for an unrecognized gender or a lookup error. If your table uses upper bounds or has a maximum valid weight, adjust the lookup logic and add the necessary boundary checks.
WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook, or create a new spreadsheet.
  2. 2. Arrange the table: Enter ascending female thresholds in N2:N22, male thresholds in O2:O22, and corresponding percentile values in P2:P22.
  3. 3. Enter the inputs: Type the weight in F2 and F or M in H2.
  4. 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. 5. Test and save: Check the result against known thresholds, then use File > Save As to save the workbook in XLSX format.
Look up percentiles using separate female and male weight thresholdsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and a familiar interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.