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
- Click Module on the Insert menu
- Copy and paste vba code.
- Exit visual basic editor
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
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.
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