Function Problems

Add a Gender Condition to INDEX and MATCH in Excel

Elise WilliamsElise Williams Nov 18, 2026 868 views

Question details

Return height and weight classifications only in the columns that match each person's gender.

Add a Gender Condition to INDEX and MATCH in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the female height formula

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

2
Enter the male height formula

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

3
Enter the weight formulas

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

4
Fill the formulas down

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.

Wrap each INDEX and MATCH formula in a gender check
How the threshold lookup works: MATCH with 1 finds the largest threshold less than or equal to the measurement. A value below the lowest threshold produces the displayed error message. These formulas classify height and weight separately; they do not calculate BMI.
Work with spreadsheet formulas

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. 1. Open the workbook: In WPS Office, open Spreadsheet and select Open to choose your workbook.
  2. 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. 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. 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.
Keep gender-specific classification results in separate columns.Open and save Excel-compatible XLSX and XLS files.Use familiar spreadsheet formulas and a fill handle.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

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.