Repeat a column value based on another column value across rows in Microsoft Excel

Question

In my Excel sheet, I want Excel to automatically fill in a column value based on the value from the same column in previous rows.

Specifically, consider the following example data:

	A	B	C
1	abcd	56.1	25
2	bcde	20.0	96
3	abcd	75.4	25

The value in column C of the current row should be automatically filled in with the value from column C in a previous row where the column A values match.

Using the example data above, where the current row is row 3, then C3 should be populated with the value from C1 (25) because A3 matches A1.

Notes

• I may overwrite the formula in some rows;
• There may be multiple matches;
• In the case of multiple matches, the match with the highest row number should be returned;

Is there a way to configure Excel such that it auto-fills column C based on matching the value in column A to prior rows?

Answer 1

Formula in C4

=IFERROR(
     INDEX($C$1:C3, XMATCH(A4, $A$1:A3,0,-1)),
     "")

1. Formula works in both Sheets and Excel;
2. Formula uses XMATCH to return the position of the value in column A where
   • it matches the value in the current row's column A;
   • the row number is less than the current row;
   • rows are searched in reverse order, which will return the result with the highest row number (most recent)
3. Result from XMATCH is used to identify the row to return the value in column C;
4. IFERROR returns a null value where XMATCH errors out (no match);

Matched Value

 

Most Recent Matched Value

 

No Match