3

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?

3
  • This will need to use vba in a worksheet_change event. You cannot have a cell with a formula that the user can ALSO input values. Mar 22, 2023 at 16:45
  • 1
    To clarify ScottCraner's comment you can "have a cell with a formula that the user can ALSO input values" however entering a value would effectively overwrite/delete the formula which may or may not be okay depending on what you are doing.
    – Blindspots
    Mar 22, 2023 at 18:46
  • 1
    Overwriting the formula is fine for me. I can type out a formula in the first row and drag it all the way down. Then, I can type out custom value when required and leave the formula when it is correct. Is that possible? Mar 22, 2023 at 18:54

1 Answer 1

3
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

 

2
  • 1
    Perfect! Thank you so much. I'll check the edits and approve them (if not applied already). Mar 22, 2023 at 21:10
  • 1
    NP. I deleted my comments as no longer relevant.
    – Blindspots
    Mar 23, 2023 at 15:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .