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

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:

**How to enter an array formula**

- Select cell range C24:C26
- Copy above formula
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter once
- 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?**

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

### User defined function category

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a user definedÂ function that lists files in a ggiven folder and subfolders. A user defined function is […]

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

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

This blog post describes how to createÂ permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

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

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]

The SUBSTITUTE and REPLACE functions can only handle one string,Â the following User-Defined Function (UDF) allows you to substitute multiple […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

This blog post demonstrates a custom function (UDF) that createsÂ permutations. Repetition is allowed. The custom function lets you specify the […]

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

This article describes how to find a sum from a range of numbers using a user defined function. LetÂ´s see […]

## Functions in this article

More than 1300 Excel formulas

## Excel categories

### 5 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

Paste image link to your comment.

**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.

I set up the data table above and have tested the Function. If the x & y value are in the first table (Table 1), everything works fine. If the data is not in the first table, but the second or third table, then it returns "#VALUE!" Is there something I am doing wrong or is this a possible bug in the function?

Bogey,

you are right, it is a bug.

I have updated this article and the attached file.

Thank you for telling me.

/Oscar