Function Problems

Replace a Long Excel IFS Formula with INDEX and MATCH

Steve KSteve K Nov 18, 2026 868 views

Question details

Replace a long IFS formula with a shorter lookup formula for OUT/Pax and IN/Pax values.

Replace a Long Excel IFS Formula with INDEX and MATCH
Product
Microsoft Excel
Device & OS
not provided
Scenario
The PRICE LIST worksheet needs to look up each Price Validity value in the DONNEES worksheet and return the corresponding OUT/Pax and IN/Pax values.
Observed behavior
A single formula can be filled across and down to return both values, with a blank displayed when no match is found.
Before you start

Check that Price Validity is in column B of PRICE LIST and column J of DONNEES, and that the return values are in columns K and L of DONNEES. Adjust the references if your workbook uses different columns or more than 40 source rows.

Solution 1Recommended

Use INDEX and MATCH to return both passenger values

Use this formula when you want an exact Price Validity lookup that can be filled across both result columns.

1
Enter the lookup formula

On PRICE LIST, select the first result cell, such as K2, and enter =IFERROR(INDEX(DONNEES!K$2:K$40,MATCH($B2,DONNEES!$J$2:$J$40,0)),"").

2
Fill the formula right

Select K2 and drag its fill handle to L2. The return range changes from DONNEES column K to column L, while the lookup column stays fixed.

3
Fill the results down

Select K2:L2 and drag the fill handle down through the last PRICE LIST row, such as row 200.

Use INDEX and MATCH to return both passenger values
How the lookup handles matches and errors: MATCH uses 0 for an exact match and returns the first matching row if Price Validity appears more than once. IFERROR displays a blank for any formula error, including a missing match.
Use WPS Spreadsheets

Look up passenger values with INDEX and MATCH in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and use the same INDEX and MATCH formula to return values from DONNEES, then fill it across and down.

  1. 1. Open the workbook: In WPS Spreadsheets, use Open to select the workbook containing PRICE LIST and DONNEES.
  2. 2. Enter the formula: On PRICE LIST, select the first result cell, such as K2, and enter =IFERROR(INDEX(DONNEES!K$2:K$40,MATCH($B2,DONNEES!$J$2:$J$40,0)),"").
  3. 3. Fill across and down: Drag the fill handle from K2 to L2, then select K2:L2 and drag down through the remaining PRICE LIST rows.
  4. 4. Save the workbook: Use File > Save or Save As to save the updated workbook in an Excel-compatible format.
Replace a long IFS formula with an exact-match lookupOpen and edit Microsoft Excel XLSX and XLS filesFill one formula across multiple result columnsUse familiar spreadsheet functions and cell references
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return a blank?

IFERROR returns a blank when the formula encounters an error, including when MATCH cannot find the value from PRICE LIST cell B2 in DONNEES!J2:J40. Check the lookup value, source range, and formula references.

What happens if Price Validity appears more than once in DONNEES?

MATCH returns the position of the first exact match in DONNEES!J2:J40, so INDEX returns the value from that first matching row.

How do I include more than 40 rows from DONNEES?

Change both source ranges in the formula to the same last row. For example, use DONNEES!K$2:K$200 and DONNEES!$J$2:$J$200, then fill the revised formula across and down.

Why can the formula be filled from OUT/Pax to IN/Pax?

The return column in DONNEES!K$2:K$40 is relative, so it changes to column L when you fill right. The dollar signs keep the MATCH lookup range in column J and the Price Validity reference in column B.