Vlookup with Date

🎯 Vlookup with Date by using Concatenate:

You want to use VLOOKUP to match based on multiple columns (e.g., Emp. Code + Attendance Date) by combining them into a single helper column, both in your main sheet and in the lookup sheet (i.e., Master Data Sheet(Q4)).

🛠️ Steps to Use VLOOKUP with CONCATENATE:

Step 1: Add a Helper Column in Both Sheets

🔹 In Master Data Sheet(Q4):

  1. Insert a new column before column B or at the start.

  2. In the new column (let’s say it's now column A), enter the formula to combine Emp. Code and Attendance Date:

    =B2 & C2 Or =CONCATENATE(B2, C2)

    (Assuming B = Emp. Code, C = Attendance Date)

  3. Drag this formula down to apply to the whole dataset.

  4. This will create unique lookup keys like 266332621/2/2025.

Step 2: Add a Helper Column in the Current Sheet (Vlookup with Dates)

  1. Insert a new column before column F or wherever needed.

  2. In the new column (assume column G), use the same method to combine values:


    =A3 & C3 Or =CONCATENATE(A3, C3)

    This ensures your lookup value matches the format created in the master sheet.

Step 3: Use VLOOKUP with the Combined Key

In your Status column (e.g., cell F3), use:

=VLOOKUP(G3, 'Master Data Sheet(Q4)'!A:I, 9, FALSE)

Explanation:

  • G3 is your combined lookup key (Emp. Code + Attendance Date)

  • 'Master Data Sheet(Q4)'!A:I is the table range including the new helper column

  • 9 is the column index for Status (adjust if the structure changes)

  • FALSE ensures exact match

✅ Tips:

  • Make sure both combined values (Emp Code + Date) are in the same format (e.g., no extra spaces, consistent date formatting).

  • To avoid date format mismatches, wrap dates with TEXT(): 

    =A3 & TEXT(C3, "mm/dd/yyyy")

    and

    =B2 & TEXT(C2, "mm/dd/yyyy")

🧪 Example:

In Master Data Sheet(Q4):

A (Helper)B (Emp Code)C (Date)...I (Status)
266332261/2/2025266332261/2/2025...P

In VLookup with Dates:

A (Emp Code)C (Date)G (Helper)F (Status)
266332261/2/2025266332261/2/2025P