How to Use XLOOKUP for Reverse Order Search

Bottom Line: Learn how to use XLOOKUP to search in reverse order from bottom to top (last-to-first) to return the last date.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

The file that I work within the video can be found below. You can also download the “Follow Along” version if you'd like to practice building out the report.

Dunder Mifflin Attendance Report - Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report - Final.xlsx (166.6 KB)

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I'm planning to post a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Building an Attendance Dashboard

This post is part 2 of a six-part series explaining how to build an interactive dashboard for attendance. This attendance report was an entry for the Excel Hash competition. I recommend you check out the first post here:

How to Use the XOR Function

Using XLOOKUP

In the first part, we determined which entries are “In” and which are “Out,” using the XOR function. Now we need to determine how long each employee was in the building so we can calculate their total time at work.

To do this, we are going to utilize a helpful new function called XLOOKUP (article & video on XLOOKUP). Among other great abilities, this function can help us to search a set of entries from in reverse order, from last to first. This feature is helpful because for any “out” entry, we can find the “in” entry that was directly before it, and then calculate the duration between them.

XLookup from bottom to top

The XLOOKUP function has six arguments. The last 3 are optional but used in this scenario:

  1. Lookup Value: This is what you want the function to search for. For our example, we are looking for an employee's name.
  2. Lookup Array: This is the range that you want it to search in. In our case, we want to search for everything above the current cell (see running total reference section below).
  3. Return Array: The range that you want to return a value from. For our report, we want to return the date/time entry.
  4. If Not Found: The value to return if the lookup value is not found in the lookup array. I just used “Not Found” but you could choose any word or value, including a blank. We can use this in place of wrapping the function in IFERROR.
  5. Match Mode: Allows you to specify an exact or approximate match. I chose the “Exact match” to find the exact employee name, represented by 0.
  6. Search Mode: Allows you to search in regular or reverse order. In our case, we want to search last-to-first, represented by -1.

Running Total References

For the lookup and return arrays we are using a running total reference. This is a mixed absolute/relative reference that refers to all the cells at and above the row the formula is in.

If the formula is in row 7, the range reference will look like the following.

A$1:A7

The starting row will always be anchored at row 1. This allows to always perform the search above the current row to find the previous occurrence of the employee's name.

When all is said and done, our XLOOKUP function in Row 2 of our spreadsheet looks like this:

=XLOOKUP(A2,A$1:A1,B$1:B1,”Not Found”,0,-1)

Xlookup function with regular ranges
Click to Enlarge

Calculating the Duration

Once we have plugged in the XLOOKUP values, we can calculate the duration. This is just a simple formula that subtracts the previous timestamp from the current timestamp. The result will most likely be formatted as a decimal indicating a fraction of the day. If so, just multiply that result by 24 hours.

Now, we only want to calculate duration on the “Out” entries (subtracting the time the employee arrived from the time the employee left). If we subtracted their out time from their in time, we would be calculating the duration of time they spent away from the office, which is not what we are looking for.

To make sure that we're only dealing with time spent at work, we can just wrap our existing formula in an If Statement:

If the value in the In/Out column is “Out” then return the answer, and if not, return a zero.

Running Totals with Structured References

As I described in my previous post, using normal references can sometimes lead to errors when new entries are added to your Tabel. To avoid this, you can build the same formula using the INDEX function and structured (table) references.

The following formula is used for the lookup array in XLOOKUP.

INDEX(tblData[[#All],[Employee]],1):INDEX(tblData[[#All],[Employee]],ROW()-1)

This formula creates a reference from the header row to the cell ABOVE the row that the formula is in. It uses the INDEX function and the ROW function to create a reference to the cell above.

The final formula looks long and scary but isn't too bad once you break it down into pieces. The color-coding in the image below might help you to better break it down.

Xlookup function with structured references

Click to Enlarge

I walk you through these structured references in more detail in the video.

Conclusion

In the next video, we will use these duration totals that we calculated to analyze employee attendance. We'll be able to break down attendance hours for individuals as well as departments.

I hope this post was helpful in explaining how we can use the XLOOKUP function in order to search last-to-first and find the prior occurrence of a particular entry. Feel free to ask questions or leave feedback in the comments below!

3 comments

Your email address will not be published. Required fields are marked *

  • Unfortunately think that this function is only available for Excel 365 users.
    This should be made more explicit
    Need to adjust feed to ensure that users are properly informed!!!

    • Hi Malcolm,
      Thank you for the feedback. I did mention that in the Download section above, but we’ll try to make it more clear. I will also be doing a post on how to create this file with pivot tables, which will work in all versions of Excel. Stay tuned…

      • Hi, Been using the lookup function, as opposed to V / Hlookup.

        How do I make it return a false value only? Tried adding this to the formula, but ma being told too many arguments.

        Thanks

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly