I want to extract the value of a cell that corresponds to another cell from the row above, if a criteria is met. For example, if Cells (N, X).value = ABC, then I need the value in Cells (N+1, T). Additionally, I would need to have this "function" directly connected to the range, as the range needs to continually change based on the value of the loop variable.
I have been using the index and match function in VBA, but am not sure if this is the best approach

I was able to get the correct numbers I am looking for within Excel (Not in VBA) using the following formula: Index (A1:A1283,Match("Short",N1:N1283,)+1),).
A1:A1283 is the column that has the cells I would like to extract. "Short" is the keyword.

I have no idea how to put this into VBA. I also have this formula inside of a loop because I want the ranges to continually change as the loop goes, so instead of A1 I would be using Ai; with "i" being the loop number.Would this work?

I have been looking on the internet to try to figure it out, but I have had no luck. Please let me know if anyone can help. Thank you so much.