Forum Discussion
Return value of lowest row (offset a few columns) between 2 cells after VLookup
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
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...
Spike08096 Here's an example of what I mean. No need to keep track of this in formatted tables per employee.
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.
- 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.
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...
