Microsoft Office Professional 2010 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, 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, Worksheets(DSheet).Range(strRRange),Microsoft Office Professional 2010, 0)DCol = DCol + (Lookup_Row.Column - 1) + Column_Offset Set ARange = Range(Cells(DRow, DCol),Microsoft Office 2007 Product Key, 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, 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, Worksheets(DSheet).Range(strCRange), 0)DRow = DRow + Lookup_Range.Row - 1 Set RRange = Range(Cells(TRow,Office 2010 Key, 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, 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,Office 2010 Key, _ 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,Windows 7 Ultimate, DCol), Cells(DRow, DCol))strARange = ARange.Address XLOOKUP = Worksheets(DSheet).Range(strARange) End Function <div