Author: Oscar Cronquist Article last updated on December 02, 2018

This post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can say that this UDF searches in three dimensions or a three-dimensional lookup based on if a value is found in a table or not.

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