Find Customer's Last Shipment Date with Excel Magic

Table of Contents

1. Introduction
2. How to Download the Workbook
3. Finding the Latest Shipment Date and Rate
   1. Example 1: Sorted Date Column
   2. Example 2: Unsorted Date Column
4. Using the Lookup Function
   1. Using Lookup Match
   2. Handling Divide by Zero Errors
5. Dealing with Unsorted Columns
   1. Using the MAX Function
   2. Finding the Biggest Date for Each Product
6. Conclusion

Introduction

Welcome to Excel M Trick Number 942! In this video, we will learn how to find the latest shipment date and rate for any given product in a dataset. We will explore two examples - one with a sorted date column and another with an unsorted date column. Let's dive in!

How to Download the Workbook

Before we begin, if You want to download the workbook for this tutorial, simply click on the link provided below the video.

Finding the Latest Shipment Date and Rate

In this section, we will discuss two scenarios - one where the date column is sorted and another where it is not.

Example 1: Sorted Date Column

When the date column is sorted, finding the latest shipment date and rate becomes much easier. We can simply select the last occurrence of the desired product and retrieve the corresponding date and rate.

To do this, we will use an array formula. Copy the formula over and down the range, making sure to lock the cells appropriately. By comparing the product names to the one entered, we can identify the last occurrence and retrieve the date and rate associated with it.

If you encounter divide by zero errors, you can use the Lookup function with a large enough number to always go to the last occurrence. This will ensure that the formula only considers the latest date and rate.

Example 2: Unsorted Date Column

When the date column is not sorted, we cannot rely on the last occurrence of the product to find the latest date. In this Scenario, we will use the MAX function to identify the biggest date associated with each product.

Using the MAX function, we can extract the dates from the array of TRUE/FALSE values. By comparing these dates to the product names, we can determine the respective positions. This allows us to retrieve the biggest date and rate for each product.

Conclusion

In conclusion, finding the latest shipment date and rate in Excel can be achieved through various techniques depending on the sorting of the date column. Using array formulas, the Lookup function, and the MAX function, we can accurately determine the desired information. Happy Excel-ing!

Highlights

• Learn how to find the latest shipment date and rate in Excel
• Two examples: sorted and unsorted date columns
• Use array formulas and the Lookup function for sorted columns
• Utilize the MAX function for unsorted columns

FAQs

Q: Can I download the workbook used in this tutorial? A: Yes, you can download the workbook by clicking on the link provided below the video.

Q: How do I handle divide by zero errors when using array formulas? A: To handle divide by zero errors, you can employ the Lookup function with a large enough number to always go to the last occurrence. This ensures that the formula only considers the latest date and rate.

Q: What if the date column is not sorted? A: If the date column is not sorted, you cannot rely on the last occurrence to find the latest date. In this case, you can use the MAX function to identify the biggest date associated with each product.