Formula for Looking Up 2nd Date for a Name Across Different Sheets

Objective

The aim is to create a formula in Excel that looks up the second occurrence of a specific name across multiple sheets and returns the corresponding date.

Formula Structure

The formula will utilize a combination of functions such as INDEX, MATCH, IF, and ROW to achieve the desired result. Below is the structured formula:

=INDEX(Sheet1!B:B, SMALL(IF(Sheet1!A:A=Name, ROW(Sheet1!A:A)-ROW(Sheet1!A$1)+1), 2))

Explanation of the Formula

1. INDEX Function:

   • INDEX(Sheet1!B:B, ...) retrieves data from column B on Sheet1, where desired dates are located.

2. SMALL Function:

   • SMALL(..., 2) is used here to get the second smallest value, which corresponds to the second occurrence of the name.

3. IF Function:

   • IF(Sheet1!A:A=Name, ROW(Sheet1!A:A)-ROW(Sheet1!A$1)+1) creates an array of row numbers where the name matches.

4. ROW Function:

   • ROW(Sheet1!A:A)-ROW(Sheet1!A$1)+1 returns the relative row numbers for matches.

5. Array Formula:

   • This formula must be entered as an array formula. After typing, instead of pressing Enter, press Ctrl + Shift + Enter to activate it.

Practical Example

Data Setup

Assuming the following data structure across different sheets:

Sheet1

Sheet2

Implementation

To find the second date for "Alice":

1. Place the formula in a cell on any sheet (let's say Sheet3):

=INDEX(Sheet1!B:B, SMALL(IF(Sheet1!A:A="Alice", ROW(Sheet1!A:A)-ROW(Sheet1!A$1)+1), 2))

2. If "Alice" has entries at index 1 and 3 (in Sheet1) and additional entries on Sheet2, applying the formula on both sheets summarily would require adjustments to be concatenated with additional logic for multiple sheets.

Notes

• This formula can be adapted for additional sheets by wrapping the logic within surrounding logical structures (like additional IF conditions) or using a script-based approach for more extensive datasets.
• Users are encouraged to utilize resources and courses available on the Enterprise DNA Platform for further learning on advanced data manipulation techniques in Excel.

This approach provides a succinct yet detailed pathway to retrieving the second occurrence of a name's associated date from multiple sheets within a workbook.