Excel Video 362 takes our SMALL functionality to the next level by adding two more functions to the mix. If VLOOKUP is new to you, it might help to watch the VLOOKUP playlist starting with Excel Video 61. If you aren’t familiar with WEEKDAY, watch Excel Video 165. We’ll use WEEKDAY to calculate the date of the week in column B and VLOOKUP to look up a matching number for the day of the week we enter in cell F22. If those numbers match and the appointment type and location match, the array picks the next available appointment.
We’ve made this array fairly complex, but the point is that arrays can quickly loop through a range of cells, apply a series of formulas, and return a quick answer. You might have to troubleshoot the array formula the first time, but once it works, you’ve got a powerful way to analyze a lot of data quickly.
Stay tuned. Instead of looking for the first, second, or third next available future appointment, next time we’ll look to the past to see the last referral from a set of referring physicians. I look forward to seeing you then.
