Replace a Long Excel IFS Formula with INDEX and MATCH
Question details
Replace a long IFS formula with a shorter lookup formula for OUT/Pax and IN/Pax values.

- 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.
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.
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.
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)),"").
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.
Select K2:L2 and drag the fill handle down through the last PRICE LIST row, such as row 200.

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. Open the workbook: In WPS Spreadsheets, use Open to select the workbook containing PRICE LIST and DONNEES.
- 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. 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. Save the workbook: Use File > Save or Save As to save the updated workbook in an Excel-compatible format.

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.




