Function Problems

Use Excel Formulas to Fill Dates, Costs, and Stores for Repeated Items

John WilsonJohn Wilson Nov 13, 2026 869 views

Question details

Automatically retrieve an item's existing date, cost, and store when the same item is entered again in an Excel schedule.

Use Excel Formulas to Fill Dates, Costs, and Stores for Repeated Items
Product
Microsoft Excel
Device & OS
not provided
Scenario
An existing table contains item names and their associated details. New schedule entries should reuse those details without manual retyping.
Observed behavior
Entering a previously recorded item should populate the corresponding date, cost, and store cells automatically.
Before you start

Confirm that your lookup table has item names in its first column, followed by dates, costs, and stores. The examples assume reference data in A1:D100; adjust the range to exclude headers and include your actual records.

Solution 1Recommended

Populate repeated items below the original Excel table

Use this formula when your reference records are in A1:D100 and new items start in cell A101.

1
Enter the repeated item

In cell A101, enter an item name that already exists in column A of the reference table.

2
Add the lookup formula

Select B101 and enter =IFERROR(VLOOKUP($A101,$A$1:$D$100,COLUMN(),FALSE),""). In column B, COLUMN() returns 2, so VLOOKUP retrieves the date from the second column of the reference table.

3
Fill the remaining details

Drag the fill handle from B101 across to D101. The column index becomes 3 in column C and 4 in column D, returning the cost and store.

4
Extend and format the schedule

Select B101:D101 and drag the fill handle down through your new entry rows. Format column B as Date and column C as Currency using Home > Number Format.

Populate repeated items below the original Excel table
Keep reference records separate from formula results: The fixed range $A$1:$D$100 searches only the original records. Extend it when you add reference data, but do not include formula-generated rows in the lookup range.
Fill repeated-item details with WPS

Automatically Fill Repeated Item Details in WPS Spreadsheets

WPS Spreadsheets can use the same exact-match VLOOKUP formula to retrieve dates, costs, and stores from an existing item table. Open your Excel workbook, add the formula, and fill it across and down.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to load your Excel workbook. Confirm that item names occupy column A and their details occupy columns B:D.
  2. 2. Enter the item and formula: Enter a repeated item in A101. Select B101 and enter =IFERROR(VLOOKUP($A101,$A$1:$D$100,COLUMN(),FALSE),""). Adjust the row and reference range to match your sheet.
  3. 3. Fill the detail cells: Drag the fill handle from B101 to D101, then select B101:D101 and fill down for additional schedule rows.
  4. 4. Format and save: Use Home > Number Format to apply Date formatting to the date cells and Currency formatting to the cost cells. Choose Save As and select .xlsx to keep the workbook in Excel format.
Reuse existing dates, costs, and stores for repeated items.Supports Microsoft Excel .xlsx and .xls workbook formats.Use familiar VLOOKUP, IFERROR, and fill-handle workflows.Keep unmatched item entries visually blank with IFERROR.
microsoft office alternative - wps office

Frequently Asked Questions

Which record is returned if an item appears more than once?

VLOOKUP with FALSE returns the first exact match from top to bottom. If repeated items have different dates or costs, sort or organize your reference records so the intended record is first, or use a lookup designed for the latest match.

Why are the result cells blank even though the item exists?

IFERROR hides lookup errors. Check that the item is inside the reference range and that its spelling, spaces, and value type match. Temporarily remove IFERROR from the formula to see the underlying error. For results starting in H2, use COLUMN()-6 rather than COLUMN().

Why does the retrieved date appear as a number?

Excel stores dates as serial numbers. Select the result cells and choose Home > Number Format > Short Date or Long Date to display them as dates.

Can new schedule entries become reference records automatically?

The fixed range $A$1:$D$100 does not expand automatically. Add genuine source records to a separate reference table and update the lookup range as needed. Avoid expanding the range to include the formula result cells, which can introduce circular references.

Will this formula create a new record for an item that is not found?

No. VLOOKUP only retrieves existing records, and IFERROR displays a blank when no match is found. Add the new item's date, cost, and store to the reference table before expecting its details to populate.