Author: Oscar Cronquist Article last updated on March 16, 2019

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A User defined function is a custom function anyone can use, simply copy the VBA code and paste to your workbooks code module and you are good to go.

The custom function demonstrated in this article accepts an arbitrary number of range arguments (table ranges). The custom function returns the first match in each table if there is a match.

Array formula in cell C24:C26:

=SearchMultipleTbl(D22,D23,B3:F6,B9:F12,B16:E20)

How to enter an array formula

  1. Select cell range C24:C26
  2. Copy above formula
  3. Paste formula
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. Release all keys

User Defined Function Syntax

SEARCHMULTIPLETBL(xaxis, yaxis, cellrange1, [cellrange2])

Arguments

Parameter Text
xaxis Required. The x-axis condition you want to search for.
yaxis Required. The y-axis condition you want to search for.
cellrange1 Required. The range you want to add.
[cellrange2] Optional. You may have up to 255 additional argument cell ranges.

VBA Code:

'Name function and arguments
Function SEARCHMULTIPLETBL(xaxis As Variant, yaxis As Variant, ParamArray cellranges() As Variant)
'Declare variables and data types
Dim i As Integer, x As Variant, y As Variant
Dim temp() As Variant, xrange As Range, yrange As Range
'Redimension array variable temp so it may grow
ReDim temp(0)

'Iterate through all cell ranges
For i = LBound(cellranges) To UBound(cellranges)
    'Enable error handling
    On Error Resume Next
    'Save first row to xrange object
    Set xrange = cellranges(i).Rows(1)
    'Save first column to yrange object
    Set yrange = cellranges(i).Columns(1)
        'Find position of matching value in row and column
        x = Application.WorksheetFunction.Match(xaxis, xrange, 0)
        y = Application.WorksheetFunction.Match(yaxis, yrange, 0)
        'If found an error is not returned and Err.Number becomes 0 (zero)
        If Err.Number = 0 Then
            temp(UBound(temp)) = cellranges(i).Rows(y).Columns(x).Value
            ReDim Preserve temp(UBound(temp) + 1)
        Else
            'Disable error handling
            On Error GoTo 0
        End If
'Continue with next cell range
Next i
'Remove last container in array
ReDim Preserve temp(UBound(temp) - 1)
'Return values in temp array rearranged (transposed)
SEARCHMULTIPLETBL= Application.Transpose(temp)

End Function

Where to copy vba code?

  1. Press Alt-F11 to open the Visual Basic Editor
  2. Press with left mouse button on Module on the Insert menu
  3. Copy and paste vba code.
  4. Exit visual basic editor

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Filter unique distinct values (case sensitive) [UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

Dynamic Gantt charts

Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]

Change chart series by clicking on data [VBA]

The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]