Forum Discussion
Return value of lowest row (offset a few columns) between 2 cells after VLookup
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...
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.
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...
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.
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.
