There is a matching Excel file (called Excel Magic) that you can use to follow along and practice, which you can download from here.
One of Excel's weak points is that it's not easy to "join" two datasets together, like you can in SQL programs or other programming languages. But Excel does have VLOOKUP, HLOOKUP and -- a recent addition -- XLOOKUP.
The difference between VLOOKUP and HLOOKUP is that VLOOKUP will troll through your lookup table vertically (all in one column). HLOOKUP goes through it horizontally, or all in one row.
Both functions are useful for cases where you have data that relates to another chunk of data, with one field in common. They work best if you have the data in the same workbook, but it can be on separate worksheets. This might be the list of 50 states with current Census estimate data in one worksheet and the same list with last year’s data in another worksheet. Or it might be a one-to-many relationship where you have a list of cell phone calls and another table that groups the time of day into categories (such as morning, evening and afternoon).
Starting with versions of Excel released in 2020, it now has another function called XLOOKUP that works much the same way but allows you to move multiple columns from one table to the other in one swoop. In other words, it's more powerful than VLOOKUP.
To get matching data, download from here and use the sheets called XLOOKUP-1 and XLOOKUP-2
Follow along with the first dataset and the second dataset
use worksheet in Excel Magic called “classify”
This is crime report data that tells me the date and time of the incident, but I want to add a column that identifies which police shift the call came in on. I’ve heard that some shifts are particularly bad about ignoring calls that come in just before shift change. So I’ve created a table indicating the start of each shift.
Note that I have the night shift in there twice. That’s because I need to tell Excel what to do with the times that occur just after midnight. Without that, Excel doesn’t know what to do with the calls that occur between midnight and 6 am.
Also note another important point --- the table is in chronological order. This is important when you’re doing an inexact match.
The reason is that Excel is going to take the time of each call and compare it to this lookup table, first determining whether it falls at or after 12:00 am, but before 6 a.m. If not, then it will move down to the next one.
The only thing different in this VLOOKUP formula compared to the first one we did is that the final argument is TRUE.