XLOOKUP function


✅ XLOOKUP:

Pull data from the "Data sheet" for multiple employees based on Employee Code No across 30 date columns.

🛠️ XLOOKUP Syntax Refresher: 

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

🧭 Step-by-Step Process Flow:

1. Prepare Your Sheets

  • Sheet 1 (Main Sheet): This is where you're writing the XLOOKUP function (like the image shown).

  • Sheet 2 ("Data sheet"): Contains employee data with Employee Code No in column B and corresponding values from column C to AF (30 columns).

2. Identify Key Columns

  • B3 in Main Sheet: Contains Employee Code No (used as the lookup_value).

  • 'Data sheet'!B:B: Column B in the Data sheet (lookup_array).

  • 'Data sheet'!C:AF: Columns C to AF hold the data you want to return (30 columns).

3. Set Up First XLOOKUP Formula

In cell D3 on the Main Sheet: =XLOOKUP(B3, 'Data sheet'!B:B, 'Data sheet'!C:C)

This pulls the value from the first date column (e.g., "01-01-17").

4. Drag Formula Across 30 Columns

You cannot drag the range 'Data sheet'!C:C to D:D, E:E, etc., directly. So you need to dynamically shift the return column as you drag the formula across.

✅ Use INDEX to make it dynamic:

In D3, enter: 

=XLOOKUP($B3, 'Data sheet'!B:B, INDEX('Data sheet'!C:AF,,COLUMN(D3)-COLUMN($D$2)+1))

🔍 Explanation:

  • $B3: Fixed column, so lookup_value stays as Employee Code No.

  • 'Data sheet'!B:B: Lookup range for Emp Code No.

  • INDEX('Data sheet'!C:AF,,COLUMN(D3)-COLUMN($D$2)+1): Dynamically selects the correct return column as you move from D to E to F…

    • COLUMN(D3)-COLUMN($D$2)+1 = 1 in D3, 2 in E3, etc.

5. Apply to All Rows & Columns

  • Drag this formula across 30 columns (D to AG if dates start in D).

  • Then drag down for all employees.

📌 Tips:

  • Freeze the row for lookup column ($B3) to keep lookup value intact.

  • Adjust the date headers as per your actual data in "Data sheet".

  • Make sure the column count matches exactly with what's in 'Data sheet'!C:AF.

✅ Example with Better Readability:

In cell D3: 

=XLOOKUP($B3, 'Data sheet'!B:B, INDEX('Data sheet'!C:AF,,COLUMN()-COLUMN($D$2)+1))

As you copy across:

  • In E3, it becomes COLUMN()-COLUMN($D$2)+1 = 2

  • In F3, it becomes 3, and so on.