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

[Spike08096]

Hello, I have decent excel experience, but this eludes me. Example: I have one big Excel file using only columns A-D (Roughly 10,000 rows) that contains employee ID#'s and their PTO balance/bank info below their name. Each month, we insert a row below the latest entry for that person to enter their new PTO balance information. Also, once every year that balance gets reset, and we place a full blank line between their current and previous lines to mark it.

Can I get assistance with a VLookUp formula that will search for their ID# within that document, then return the latest (lowest) value in column D for that person? Their information gains a row every month, and some have been here longer than others, so it would likely have to 1. Find their ID# 2. Find next persons ID# below them (All ID's are setup as ID####) 3. Limit search to the rows between those two names 4. Return lowest non-blank cell in Column D.

Table Example:

The Vlookup should return the values that I have marked in blue (They are not formatted or colored on the actual document). 

Any and all help is much appreciated! I've spent over 60 hours attempting this and still can't get it working.

-Spike

[#2]

@Spike08096 

Oh my! That's definitely not the best way to approach this. I'd start with creating on large table with just three columns. Employee ID, Hours (I presume PTO stands for Paid Time Off) and Date. Then you can use a pivot table to summarise the balance for each employee though time.

The first rows should be the starting balance of PTO for each employee at whatever point in time they started or their current balance. Then, below those rows you enter for each employee, for each month the number of PTO used as a negative number. Whenever you need to reset an employee, add the required number of hours to come to whatever their entitlement would be.

[#3]

@Spike08096 Here's an example of what I mean. No need to keep track of this in formatted tables per employee.

[#4]

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.

[#5]

@Spike08096 

Convince the Company to change a very bad practice. Just because "we have done this for 6 years already" is not a very good reason to continue with it. Keep the history but start from scratch with the current balance for all and then start doing it right. Your choice, of course.

[#6]

@Spike08096 

@Riny_van_Eekelen gives good advice. Excellent advice. You would be doing the company a huge favor by pushing for a change in the way the data has been organized. It may have begun that way--hard to know at this point--because "that's the way we did it on paper" in the good ol' days. But whatever the "reason" it's been done that way, it's really not a good way to use (dare we say abuse?) Excel. A solid single database allows Excel to do its thing  so much more effectively, reliably.

[#7 · Accepted solution]

@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...