Microsoft Office 2007 Professional Daniel’s Extre

Today’s author is Daniel Wiesenfeld, an Excel and Access Power User who is sharing his Extreme Lookup Collection with us so we can use the Excel User Defined Functions (UDFs) he created to enhance the lookup functionality. His web site danalytics.biz is currently under construction and should be available soon.In the Visual Basic Editor, insert a Module and paste the following code:' XVLOOKUP (& XHLOOKUP)' Works just like a vlookup (and hlookup) except that the user refers to a lookup colum (or row)
' rather than a range, it is 0 based and the user can "look left" (or "look upward") by using a negative
' column (or row) index.' There is also an optional argument to allow the user to offset the cell to be returned by any number
' of rows (or columns)' I do not give users the option to choose between exact or approximate match - it is always exact Function XVLOOKUP(Lookup_Column As Range, Lookup_Value As Variant, Column_Index As Integer, _ Optional Row_Offset As Integer) Dim DCol, DRow As IntegerDim DSheet,Office 2010 Discount, strCRange, strARange As StringDim ARange As Range DCol = Lookup_Column.ColumnDCol = DCol + Column_Index If IsMissing(Row_Offset) Then Row_Offset = 0End If DSheet = Lookup_Column.Parent.NamestrCRange = Lookup_Column.Address DRow = WorksheetFunction.Match(Lookup_Value, Worksheets(DSheet).Range(strCRange), 0)DRow = DRow + (Lookup_Column.Row - 1) + Row_Offset Set ARange = Range(Cells(DRow, DCol), Cells(DRow, DCol))strARange = ARange.Address XVLOOKUP = Worksheets(DSheet).Range(strARange).Value End Function Public Function XHLOOKUP(Lookup_Row As Range, Lookup_Value As Variant, Row_Index As Integer, _ Optional Column_Offset As Integer) Dim DCol, DRow As IntegerDim DSheet, strRRange, strARange As StringDim ARange As Range DRow = Lookup_Row.RowDRow = DRow + Row_Index If IsMissing(Column_Offset) Then Column_Offset = 0End If DSheet = Lookup_Row.Parent.NamestrRRange = Lookup_Row.Address DCol = WorksheetFunction.Match(Lookup_Value,Microsoft Office 2007 Professional, Worksheets(DSheet).Range(strRRange), 0)DCol = DCol + (Lookup_Row.Column - 1) + Column_Offset Set ARange = Range(Cells(DRow, DCol), Cells(DRow, DCol))strARange = ARange.Address XHLOOKUP = Worksheets(DSheet).Range(strARange).Value End Function 'XVHLOOKUP'looks up value in a range based on column and row headersPublic Function XVHLOOKUP(Lookup_Range As Range, Row_Header As Variant, Column_Header As Variant) Dim DCol,Office Professional, DRow, TRow, BRow, LCol, RCol As IntegerDim DSheet, strCRange, strRRange, strARange As StringDim CRange, RRange, ARange As RangeDSheet = Lookup_Range.Parent.Name TRow = Lookup_Range.RowBRow = TRow + Lookup_Range.Rows.Count - 1 LCol = Lookup_Range.ColumnRCol = LCol + Lookup_Range.Columns.Count - 1 Set CRange = Range(Cells(TRow, LCol), Cells(BRow, LCol))strCRange = CRange.Address DRow = WorksheetFunction.Match(Row_Header,Microsoft Office Professional Plus 2010, Worksheets(DSheet).Range(strCRange), 0)DRow = DRow + Lookup_Range.Row - 1 Set RRange = Range(Cells(TRow, LCol), Cells(TRow, RCol))strRRange = RRange.Address DCol = WorksheetFunction.Match(Column_Header, Worksheets(DSheet).Range(strRRange), 0)DCol = DCol + Lookup_Range.Column - 1 Set ARange = Range(Cells(DRow,Windows 7, DCol), Cells(DRow, DCol))strARange = ARange.Address XVHLOOKUP = Worksheets(DSheet).Range(strARange).Value End Function 'XLOOKUP'Looks up value in a range and returns value of cell that is a specified number of rows and columns
'away from lookup cells Public Function XLOOKUP(Lookup_Range As Range, Lookup_Value As Variant, _ Row_Offset As Integer, Column_Offset As Integer) Dim DRow, DCol As IntegerDim DSheet, DAddress, strARange As StringDim ARange As Range DRow = Lookup_Range.Find(Lookup_Value).RowDCol = Lookup_Range.Find(Lookup_Value).Column DRow = DRow + Row_OffsetDCol = DCol + Column_Offset DSheet = Lookup_Range.Parent.Name Set ARange = Range(Cells(DRow, DCol), Cells(DRow, DCol))strARange = ARange.Address XLOOKUP = Worksheets(DSheet).Range(strARange) End Function <div