The XLOOKUP function in Excel does not perform a case sensitive match by default. So, the values “London”, “LONDON”, and “london” would all result in a match with the XLOOKUP function.

So, how do you perform a case sensitive XLOOKUP formula?

Let’s find out. Read on or watch the video tutorial.

XLOOKUP does not Perform an Exact Match

In this example, the XLOOKUP function is returning the first matching value for the status of each individual and the [Status] in tblRates. The corresponding [Rate] is then returned by the formula.

This XLOOKUP formula is ignoring case when matching the lookup value to the text in the lookup array. This is best demonstrated by the incorrect rate returned for “Sally” in cell D4.

The formula has matched the status of “Sally” to the upper case status value from tblRates. However, we require an exact match on the lower case value found in row 4 of tblRates. The correct rate returned for “Sally” should be 350.

How to Make XLOOKUP Case Sensitive?

[Read more…] about Case Sensitive Lookup with XLOOKUP in Excel

Everyone works with dates in Excel. Often a spreadsheet will contain multiple date columns. And a common question is “how to compare dates in Excel?”.

In this tutorial, we explain how dates are stored in Excel and see many examples of comparing dates including the use of numerous functions from our Excel functions list.

Download the sample file to follow along with the practical examples.

How does Excel Store Dates in the Cells?

Excel stores dates as whole numbers known as serial numbers. Each day, one is added to the serial number of the previous date.

For example, the Excel date, or serial number, for the 1st May 2023 is 45047.

[Read more…] about How to Compare Dates in Excel