Return value of lowest row (offset a few columns) between 2 cells after VLookup

Spike08096 

---

Spike08096 wrote:
I definitely agree, but sadly this is a company wide document (Not actually PTO based) so I cannot change any formatting of it, and it has been documented this way for over 6 years (Over 4000 [ID's]). I'm just seeing what tedious daily processes I can shorten using functions with respect to what I have access to.

---

While I agree with what the others have said so far, if you are unable to make the suggested changes to the structure and layout of the spreadsheet, there are a few things you could try to get what you need:

If the main objective is to quickly find the Current Bank amount for a specific ID, you could simply use the Find and Replace feature to "jump to" the desired ID, then manually locate the last record for that ID. On the ribbon, go to Home > Find and Select > Find... (or press Ctrl+F on your keyboard), then type the ID number you're looking for (ie: ID7438), check the "Match entire cell contents" checkbox and click Find Next.

Find and Replace

If a formula is preferred, though, the following should work in any version of Excel:

=INDEX(D:D,MATCH("Start Date",INDEX(A:A,MATCH(G2,A:A,0)+2):INDEX(A:A,1048576),0)+MATCH(G2,A:A,0)-2)

Please note, this formula depends upon a consistent data structure for each ID... "Start Date" must always be spelled the same in each header row, with no leading or trailing spaces, and each data range for each ID must be separated by a single blank row (no more, no less). Also, you would need to include a "blank" ID + header row at the very bottom of the spreadsheet, so the formula will work for the final ID number.

Index / Match Results

If you are using Excel for MS365 or Excel for the Web, the formula could be simplified as follows:

=LET(n, XMATCH(G2, A:A), INDEX(D:D, XMATCH("Start Date", INDEX(A:A, n+2):TAKE(A:A, -1))+n-2))

However, a more reliable approach would be to filter the data by ID and return the last non-blank record:

=LET(
    data, A1:D10000,
    start_date, TAKE(data,, 1),
    id, SCAN("", start_date, LAMBDA(a,v, IF(LEFT(v, 2)="ID", v, a))),
    TAKE(FILTER(TAKE(data,, -1), (id=G2)*(start_date<>""), "[no data]"), -1)
)

Adjust the data range reference as needed. See attached example workbook...