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