Hey there, let's figure out how to solve this Excel task without VBA—since you mentioned you're not super comfortable with formulas, I'll break this down step by step so you can easily reuse the solution even when adding new rows.

The Core Formula (Works for Most Excel Versions) #

In cell B2 (assuming your data starts at row 1 with headers), enter this formula:

=IF(OR(D2="S",D2="H"),LOOKUP(2,1/(E$1:E1<>""),F$1:F1),"")

Then you can drag this formula down to all existing rows, and copy-paste it into any new rows you add later—it’ll adjust automatically.

Let’s Break Down What Each Part Does #

• OR(D2="S",D2="H"): Checks if the current row’s column D is either "S" or "H". If not, it leaves column B blank.
• LOOKUP(2,1/(E$1:E1<>""),F$1:F1): This is the magic part that finds the value you need:
  • E$1:E1<>"": Creates a list of TRUE/FALSE values for every cell in column E from row 1 up to the row above the current one, marking non-empty cells as TRUE.
  • 1/(...): Converts those TRUE values to 1 and FALSE values to a #DIV/0! error (which LOOKUP ignores).
  • LOOKUP(2, ...): Since LOOKUP skips errors, it finds the last (closest to your current row) 1 in the list, then returns the corresponding value from column F in that same row.
• "": Leaves column B blank if column D isn’t "S" or "H".

For Excel 365/2021 Users (More Intuitive Option) #

If you have a newer Excel version, you can use XLOOKUP which is easier to read:

=IF(OR(D2="S",D2="H"),XLOOKUP(TRUE,E$1:E1<>"",F$1:F1,,,-1),"")

Here, the -1 at the end tells XLOOKUP to search upwards from the row above your current one, so it finds the closest non-empty cell in column E right away.

Optional: Handle Cases Where No Match Exists #

If there might be rows where there’s no non-empty E cell above them, wrap the lookup in IFERROR to avoid #N/A errors:

=IF(OR(D2="S",D2="H"),IFERROR(LOOKUP(2,1/(E$1:E1<>""),F$1:F1),"No matching value above"),"")

Key Tip for Reusability #

The $1 in E$1:E1 and F$1:F1 is crucial—it locks the starting row to row 1, while the ending row (the second 1) adjusts automatically as you copy the formula to new rows. That’s why it works seamlessly when you add new rows later.

内容的提问来源于stack exchange，提问作者Honduriel