Multi-Sheet Date Lookup Formula in Excel

Objective

To create a formula in Excel that looks up the next date for a specific name across multiple sheets.

Formula

Assuming you have several sheets named "Sheet1", "Sheet2", and so on, and a common structure where names are in column A and corresponding dates are in column B, you can use the combination of the INDEX, MATCH, and MIN functions to find the next date for a given name.

Here is a formula that achieves this:

=MIN(IFERROR(INDEX(Sheet1!B:B, MATCH("Name", Sheet1!A:A, 0)), ""), IFERROR(INDEX(Sheet2!B:B, MATCH("Name", Sheet2!A:A, 0)), ""), ...))

Parameters

• Replace "Name" with the name you are searching for.
• Extend the IFERROR(INDEX(...)) part for each additional sheet you want to include.

Explanation of the Formula

1. INDEX Function: This function returns the value of a cell in a specified row and column within a defined array. In this case, it retrieves the date from column B based on the row where the name is found in column A.

2. MATCH Function: It searches for a specified item (the name) in a range and returns its relative position. Here, it finds the row number of the specified name within the names listed.

3. IFERROR Function: This function captures and handles errors that occur when the INDEX function does not find a match. It allows for a blank ("") to be returned instead of an error, thereby enabling a smooth aggregation of dates.

4. MIN Function: This final function evaluates the array of dates returned by INDEX functions across different sheets and returns the minimum value, which corresponds to the next date.

Practical Example

Suppose you have the following data across two sheets:

Sheet1

Sheet2

To find the next date for "Alice", your formula would look like this:

=MIN(IFERROR(INDEX(Sheet1!B:B, MATCH("Alice", Sheet1!A:A, 0)), ""), IFERROR(INDEX(Sheet2!B:B, MATCH("Alice", Sheet2!A:A, 0)), ""))

Output

The result of the formula will be 2023-04-25, as it is the earliest date associated with the name "Alice".

Conclusion

This formula provides a systematic approach to retrieve the next date for a specified name across multiple sheets. For more complex data manipulation and analysis techniques, consider exploring additional resources and courses on the Enterprise DNA Platform.