Excel

Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula

by

The VLOOKUP function in Excel is very useful until there is a need to identify data satisfying multiple criteria. In the following example, I’ll demonstrate two different methods to perform a lookup against data that must meet multiple conditions. One method uses the SUMPRODUCT function and the other uses an array formula with MATCH and INDEX functions.

This example assumes that the data is structured in a way that will provide a unique result from the lookup. The standard use of the SUMPRODUCT and array formulas have a very different intent so if the result is not unique, you may see unexpected results.

In the sample worksheet below, there is a set of three periods specified. Column A contains the period number which is a simple sequential number representing a unique ID for the period. Column B and Column C define start and end dates for each period. There are no overlapping dates between each of the defined periods. Column E contains a small list of individual dates. I want to automatically identify which period the date in Column E belongs to in Column F. In order to belong to a period, the specific date in Column E should be greater than or equal to the period start date and less than or equal to the period end date.

Excel Multiple Criteria Lookup Sample Worksheet
Excel Multiple Criteria Lookup Sample Worksheet

Either formula should be entered starting in cell F2 (or modified to fit your particular needs).

Method 1 – Lookup using the SUMPRODUCT function

=SUMPRODUCT(--($B$2:$B$4<=E2),--($C$2:$C$4>=E2),$A$2:$A$4)

Method 2 – Lookup using an array formula with MATCH and INDEX functions

=INDEX($A$2:$A$4,MATCH(1,(($B$2:$B$4<=E2)*($C$2:$C$4>=E2)),0))

Since Method 2 uses an array formula, hit CTRL+SHIFT+ENTER after entering the formula in cell F2 otherwise Excel will show “#N/A” instead of the correct result.

Import Worksheets From One Workbook to Another Using VBA

by

The procedure ImportWorksheetsFromClosedWorkbook() may be used to copy all worksheets from one workbook (source) into a different workbook (target). The procedure also removes all references to external workbooks. This procedure may be useful if the target workbook has a series of predefined charts or pivot tables that rely on data contained in an external workbook published by another entity.

Option Explicit

Sub ImportWorksheetsFromClosedWorkbook()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim strFilename As String
    Dim strOriginalActiveWsName As String
    Dim bIsFileOpen As Boolean
    Dim objDefinedName As Object
    Dim bEnableEvents As Boolean
    Dim bDisplayAlerts As Boolean
    Dim bScreenUpdating As Boolean
    Dim i As Long
    
    bEnableEvents = Application.EnableEvents
    bDisplayAlerts = Application.DisplayAlerts
    bScreenUpdating = Application.ScreenUpdating
    
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbTarget = ActiveWorkbook
    strOriginalActiveWsName = wbTarget.ActiveSheet.Name

    strFilename = "c:\test.xlsx"
    
    If Not IsFileExist(strFilename) Then
        MsgBox "File not found: " & strFilename
        Exit Sub
    End If
    
    If IsWbOpen(strFilename) Then
        Set wbSource = Workbooks(strFilename)
        bIsFileOpen = True
    Else
        Set wbSource = Workbooks.Open(strFilename)
        bIsFileOpen = False
    End If

    For i = 1 To wbSource.Sheets.Count
        On Error Resume Next
        If Not wbTarget.Sheets(wbSource.Sheets(i).Name) Is Nothing Then
            wbTarget.Sheets(wbSource.Sheets(i).Name).Delete
        End If
        On Error GoTo 0
    Next

    For Each objDefinedName In wbTarget.Names
        objDefinedName.Delete
    Next objDefinedName

    For i = 1 To wbSource.Sheets.Count
        wbSource.Sheets(i).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
    Next

    If Not bIsFileOpen Then wbSource.Close False

    Call BreakExternalReferences
    Call DeleteExternalNames

    wbTarget.Sheets(strOriginalActiveWsName).Activate

    MsgBox "Source file successfully copied to this workbook."
     
    Application.ScreenUpdating = bScreenUpdating
    Application.DisplayAlerts = bDisplayAlerts
    Application.EnableEvents = bEnableEvents

    Set wbTarget = Nothing
    Set wbSource = Nothing
End Sub

Function IsFileExist(wbName As String) As Boolean
    IsFileExist = Len(Dir(wbName))
End Function

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
    On Error GoTo 0
End Function

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub

Excel VBA Procedures to Break All External References in Workbook

by

The following Excel VBA procedures may be used to break all external references and links in the active workbook. The BreakExternalReferences() procedure will change all formulas containing references to external workbooks into the resulting value of that formula. The DeleteExternalNames() procedure will delete all defined names that contain external workbook references. Again, any formulas containing references to the defined name will be converted to the resulting value of that formula.

Option Explicit

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub