This post describes how to lookup values in multiple cross reference tables using a user defined function.

This custom function accepts an arbitrary number of range arguments (table ranges). The custom function returns the first match in each table.

Array formula in cell C19:C21:

=SearchMultipleTbl(C17, C18, A1:E4, A6:E9, A11:D15)

How to enter array formula

  1. Select cell range C19:C21
  2. Copy paste formula into formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

VBA Code:

Function SearchMultipleTbl(xaxis As Variant, yaxis As Variant, ParamArray cellranges() As Variant)
Dim i As Integer, x As Variant, y As Variant
Dim temp() As Variant, xrange As Range, yrange As Range
ReDim temp(0)
On Error Resume Next
For i = LBound(cellranges) To UBound(cellranges)
    Set xrange = cellranges(i).Rows(1)
    Set yrange = cellranges(i).Columns(1)
        x = Application.WorksheetFunction.Match(xaxis, xrange, 0)
        y = Application.WorksheetFunction.Match(yaxis, yrange, 0)
        If Err.Number = 0 Then
            temp(UBound(temp)) = cellranges(i).Rows(y).Columns(x).Value
            ReDim Preserve temp(UBound(temp) + 1)
        Else
            On Error GoTo 0
        End If
Next i
ReDim Preserve temp(UBound(temp) - 1)
SearchMultipleTbl = Application.Transpose(temp)
End Function

Where to copy vba code?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code.
  4. Exit visual basic editor

Download excel file

Cross-reference-multiple-tables
Excel 97-2003 *.xls

Related posts:

Excel udf: Sumif across multiple sheets

Excel udf: Reorganize data

Excel udf: Combine cell ranges into a single range while eliminating blanks

Looking up data in a cross reference table in excel

Excel recursive udf: List files in a folder and subfolders