## Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula

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.

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

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 bScreenUpdating As Boolean
Dim i As Long

bEnableEvents = Application.EnableEvents
bScreenUpdating = Application.ScreenUpdating

Application.EnableEvents = False
Application.ScreenUpdating = False

Set wbTarget = ActiveWorkbook
strOriginalActiveWsName = wbTarget.ActiveSheet.Name

strFilename = "c:\test.xlsx"

If Not IsFileExist(strFilename) Then
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.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 i As Long

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

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 i As Long