## Two-way lookup using multiple tables [UDF]

*Article last updated on January 20, 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:**

**How to enter array formula**

- Select cell range C19:C21
- Copy paste formula into formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- 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?**

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

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Gets a value in a specific cell range based on a row and column number.

The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula […]

Use VLOOKUP to calculate discount percentages

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]

Two-dimensional lookup using two tables

The following formula performs a two-way lookup in two different tables. Related articles

Get date ranges from a schedule

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

Substitute multiple text strings [UDF]

The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom […]

List permutations without repetition [UDF]

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

Find positive and negative amounts that net to zero [UDF]

This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range […]

### 3 Responses to “Two-way lookup using multiple tables [UDF]”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I get errors when I change C17 and 18. And when I change D6 to "E" the results are 72,42.1 and 8.9. it appears they should be 72, 31.6, and 8.9. Why is the second number returned under results "42.1"? please help me understand.

Steve,

You are right, there was something wrong with the code.

I have changed the vba code and uploaded a new file.

Many thanks for commenting!

Wow!!! Awesome,

Thanks for sharing this extremely useful logic.