Formula Errors

Excel Formula to Return a Price by Customer, Product, and Latest Date

Natalie TaylorNatalie Taylor Nov 20, 2026 868 views

Question details

Find the selling price for a customer and product using the latest matching date.

Return a Price by Customer, Product, and Latest Date in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table contains customer names, products, dates, and selling prices. Multiple products may share the same customer and date.
Observed behavior
The desired result is the price from the row with the specified customer and product and their most recent matching date.
Before you start

Check that A2:A9 contains customers, B2:B9 contains products, C2:C9 contains valid positive Excel dates or blank cells, and D2:D9 contains prices. Enter the customer and product to look up in A12 and B12, and adjust the ranges if your table is a different size.

Solution 1Recommended

Use INDEX and MATCH to return the latest matching price

Use this formula to match both customer and product before selecting the latest date and its price.

Each inner INDEX expression returns an array of dates multiplied by the customer and product match results. Nonmatching rows become zero, so MAX finds the latest date for the specified customer and product.

MATCH finds that date in the same filtered array, and the outer INDEX returns the corresponding price. COUNTIFS leaves the result blank when no matching row has a positive date.

1
Enter the lookup values

Type the customer in A12 and the product in B12.

2
Enter the formula

Select C12 and enter =IF(COUNTIFS($A$2:$A$9,A12,$B$2:$B$9,B12,$C$2:$C$9,">0")=0,"",INDEX($D$2:$D$9,MATCH(MAX(INDEX(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9),,)),INDEX(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9),,),0),1)). Press Enter.

3
Copy the formula down

Drag the fill handle from C12 down alongside additional customer and product pairs. Update the absolute table ranges if your source data extends beyond row 9.

Use INDEX and MATCH to return the latest matching price
Duplicate latest dates: If several rows have the same customer, product, and latest date, MATCH returns the price from the first matching row in the source range.
Work with formulas in WPS Spreadsheets

Find the latest matching price in WPS Spreadsheets

Use the same INDEX, MATCH, MAX, and COUNTIFS approach in WPS Spreadsheets to look up a price by customer, product, and latest date.

  1. 1. Open your spreadsheet: Open the XLSX file in WPS Spreadsheets and check that columns A through D contain the customer, product, date, and price.
  2. 2. Enter the lookup criteria: Type a customer in A12 and a product in B12.
  3. 3. Enter the price formula: Select C12 and enter =IF(COUNTIFS($A$2:$A$9,A12,$B$2:$B$9,B12,$C$2:$C$9,">0")=0,"",INDEX($D$2:$D$9,MATCH(MAX(INDEX(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9),,)),INDEX(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9),,),0),1)). Press Enter.
  4. 4. Fill and save: Drag the fill handle down for additional lookup rows, then use File > Save to save the workbook.
Finds a price using both customer and product criteria.Works with Microsoft Excel XLSX files.Lets you copy the lookup formula down for multiple customer and product pairs.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula check both customer and product?

The comparisons against A12 and B12 ensure that MAX considers dates only for rows matching both criteria. This prevents a different product on the same customer and date from supplying the price.

What happens if there is no matching customer and product?

COUNTIFS checks for a matching row with a positive date. If it finds none, the formula returns an empty string rather than a price.

Which price is returned if the latest date appears more than once?

MATCH returns the first row in the source range with the specified customer, product, and latest date. Add another tie-breaking criterion if you need a different row.

Why might the formula return a date-related error?

Check C2:C9 for dates stored as text or other nonnumeric values. Convert them to Excel date values and make sure the formula ranges cover the same rows.