Intersection Function

Intersection Function

This function will look up a value from and intersect of the row and column. The function looks for a lookup value in the row heading (e.g. row 1) and a column heading (e.g. column A) and then returns the value at the intersect.

The function, when added to a module on a worksheet, can be used in a worksheet as a formula in the following manner:
= Intersect(lookup array, row, column)

Assuming the table is in A1:E5, and you name the range shown above something like lookupRange, this is what the worksheet function could look like =intersect(lookupRange, Feb, Justin) and the value returned would be “30”.

Name Jan Feb Mar Apr
Joe 20 22 25 23
Jerry 17 24 40 12
Justin 25 30 35 40
Jill 51 52 53 54
  • [vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Intersection function”]

    Public Function Intersection _
    (OverallRange As Range, _
    RowHeading As String, _
    ColHeading As String) As Variant

    ‘Required Parameters:
    ‘ OverallRange – the range of data to look at
    ‘ RowHeading – the heading value to look for on the left
    ‘ ColHeading – the heading value to look for at the top

    Dim RowHeads As Range, ColHeads As Range

    ‘Force the function to recalculate if the data changes, like a normal function
    Application.Volatile (True)

    ‘Locate the row heading in the first column of the range passed in
    Set RowHeads = OverallRange.Columns(1).Find(what:=RowHeading, LookIn:=xlValues, LookAt:=xlWhole)

    ‘Locate the col heading in the first row of the range passed in
    Set ColHeads = OverallRange.rows(1).Find(what:=ColHeading, LookIn:=xlValues, LookAt:=xlWhole)

    ‘Use the Intersect VBA function to find where the row and column we’ve located intersect
    Intersection = Intersect(RowHeads.EntireRow, ColHeads.EntireColumn).Value

    End Function
    [/vb]