Add a Gender Condition to INDEX and MATCH in Excel
Question details
Return height and weight classifications only in the columns that match each person's gender.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses INDEX and MATCH to classify height and weight against gender-specific reference tables.
- Observed behavior
- The desired result is for female columns to show results for F entries, male columns to show results for M entries, and the other gender's columns to remain blank.
Check the cells containing gender, height, and weight, and identify the threshold and status columns in each reference table. The example below uses F and M as gender codes and requires numeric thresholds sorted from smallest to largest.
Wrap each INDEX and MATCH formula in a gender check
Use IF to run each lookup only when the row's gender matches the result column.
In this example, column A contains F or M, column B contains height, and column C contains weight. Columns D and E will show female and male height statuses; columns F and G will show female and male weight statuses.
The example uses J2:K10 for female height thresholds and statuses, M2:N10 for male height, P2:Q10 for female weight, and S2:T10 for male weight. Adjust the ranges to fit your tables. Each threshold range must be numeric, populated, and sorted in ascending order, with its statuses aligned beside it.
Select D2 and enter =IF(UPPER(TRIM($A2))="F",IFERROR(INDEX($K$2:$K$10,MATCH($B2,$J$2:$J$10,1)),"No matching threshold"),"").
Select E2 and enter =IF(UPPER(TRIM($A2))="M",IFERROR(INDEX($N$2:$N$10,MATCH($B2,$M$2:$M$10,1)),"No matching threshold"),"").
In F2, enter =IF(UPPER(TRIM($A2))="F",IFERROR(INDEX($Q$2:$Q$10,MATCH($C2,$P$2:$P$10,1)),"No matching threshold"),""). In G2, enter =IF(UPPER(TRIM($A2))="M",IFERROR(INDEX($T$2:$T$10,MATCH($C2,$S$2:$S$10,1)),"No matching threshold"),"").
Select D2:G2 and drag the fill handle down through the remaining data rows. Check that each row displays statuses only in the columns matching its gender code.

Apply gender-specific lookups in WPS Spreadsheet
Use IF, INDEX, and MATCH in WPS Spreadsheet to display height and weight statuses in the appropriate gender columns.
- 1. Open the workbook: In WPS Office, open Spreadsheet and select Open to choose your workbook.
- 2. Check the reference tables: Locate the gender-specific height and weight thresholds and sort each threshold column from smallest to largest, keeping its status labels aligned.
- 3. Enter the formulas: Select the first result cell for each gender and measurement, then enter the corresponding IF, INDEX, and MATCH formula from the example, adjusting the cell ranges.
- 4. Fill and verify: Select the result cells and drag the fill handle down. Check that F rows show female results and M rows show male results.

Frequently Asked Questions
Why are the formulas returning a blank cell?
A blank is expected when the gender code in column A does not match the result column. The example checks for F or M after removing extra spaces and converting the code to uppercase.
What if my gender cells say Female or Male instead of F or M?
Change the IF comparisons in the formulas to "FEMALE" and "MALE", respectively. Avoid checking whether the text contains M, because the word Female also contains that letter.
Why does the formula say No matching threshold?
The measurement may be below the lowest threshold, or a referenced range may contain an error. Check the measurement and make sure the threshold range is numeric and sorted in ascending order.
Can I use an exact match instead of a threshold lookup?
Yes. Replace the 1 in MATCH with 0 if the measurement must exactly match a value in the lookup range. An unmatched value will then return the IFERROR message.




