Function Problems

Find a Value by Date and Multiply It in Excel

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Find the value associated with a date and multiply it by an amount in Excel.

Find a Value by Date and Multiply It in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The lookup date is in A2, the multiplier is in B2, and the date-value table is in D2:E155.
Observed behavior
The desired result is the matching value from column E multiplied by the amount in B2.
Before you start

Check that A2 and the dates in D2:D155 are stored as comparable Excel dates. Make sure the amounts in B2 and E2:E155 are numeric.

Solution 1Recommended

Use INDEX and MATCH for an exact date lookup

Use this formula to find the first exact match for the date in A2 and multiply its column E value by B2.

1
Select a result cell

Click an empty cell outside the lookup table, such as C2.

2
Enter the formula

In C2, enter =INDEX($E$2:$E$155,MATCH(A2,$D$2:$D$155,0))*B2 and press Enter.

3
Apply it to other rows

If other dates and amounts are listed below A2 and B2, drag the fill handle from C2 down to calculate their results.

Use INDEX and MATCH for an exact date lookup
How the lookup works: MATCH finds the first exact date match in column D, and INDEX returns the corresponding value from column E. If no exact match exists, the formula returns #N/A.
Calculate with WPS Spreadsheet

Find and multiply a date-matched value in WPS Spreadsheet

Use the same INDEX and MATCH formula in WPS Spreadsheet to retrieve a value for an exact date and multiply it by an amount.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and choose Open to select your workbook.
  2. 2. Check the lookup table: Confirm that the lookup date is in A2, the multiplier is in B2, and the date-value table is in D2:E155.
  3. 3. Enter the formula: Click C2, enter =INDEX($E$2:$E$155,MATCH(A2,$D$2:$D$155,0))*B2, and press Enter.
  4. 4. Calculate additional rows: Drag the fill handle from C2 down to apply the formula to additional dates and amounts.
Look up dates and calculate results in one formula.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and cell references.Copy the formula down for multiple dates.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #N/A?

MATCH returns #N/A when it cannot find an exact match in D2:D155. Check that A2 and the lookup dates are real dates and that neither contains an unexpected time value.

What happens if the same date appears more than once?

MATCH with 0 returns the position of the first exact match in D2:D155, so INDEX uses the corresponding value from column E.

Can I copy the formula down for other dates?

Yes. Drag the fill handle down from the result cell. The A2 and B2 references change by row, while the lookup ranges stay fixed because they use dollar signs.

How do I use a different lookup range?

Edit the formula in the result cell and replace $D$2:$D$155 and $E$2:$E$155 with your date and value ranges. Keep the ranges the same size and aligned by row.