On 7 Jan, 14:10, Gary Brown
wrote:
Buddy of mine once gave me this.
--
HTH,
Gary Brown

'
'Cell Formula using the OFFSET worksheet formula with multiple criteria to
' * * * * * find a value in the same way that the VLOOKUP worksheet formula
' * * * * * uses a single criteria to find a value.
'
'
'================================================= =========================*=
'Forumla Example 1:
'Using actual values as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")),0)}
' * * * * * * or
'Forumla Example 2:
'Using cell references as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17=A6)*--(C12:C17=A7)),0)}
'
' * * *- Remember to use CTRL-SHIFT-ENTER to make the formula an array
' * * * * * showing the '{' and "}" at the beginning and ending of the formula
'
'Note: *Just as VLOOKUP will give you the FIRST value it finds that meets it's
' * * * criteria, this formul will give you the LAST value it finds that meets
' * * * all of it's criteria.
'
'================================================= =========================*=
'
' * *DETAILED EXPLANATION OF THIS FORMULA
'
'================================================= =========================*=
'
'OFFSET worksheet function
'Returns a reference to a range that is a specified number of rows and columns
'from a cell or range of cells. The reference that is returned can be a single
'cell or a range of cells. You can specify the number of rows and the number
of
'columns to be returned.
'
'Syntax -
'OFFSET(reference,rows,cols)
'
'Reference * is the reference from which you want to base the offset.
' * * * Reference must be a reference to a cell or range of adjacent cells;
' * * * otherwise, OFFSET returns the #VALUE! error value.
'
'Rows * is the number of rows, up or down, that you want the upper-left cell
' * * * to refer to. Using 5 as the rows argument specifies that the
upper-left
' * * * cell in the reference is five rows below reference. Rows can be
' * * * positive (which means below the starting reference) or negative
' * * * (which means above the starting reference).
'
'Cols * is the number of columns, to the left or right, that you want the
' * * * upper-left cell of the result to refer to. Using 5 as the cols
' * * * argument specifies that the upper-left cell in the reference is five
' * * * columns to the right of reference. Cols can be positive (which means
' * * * to the right of the starting reference) or negative (which means to
' * * * the left of the starting reference).
'
'If rows and cols offset reference over the edge of the worksheet,
' * * * OFFSET returns the #REF! error value.
'
'Remarks -
'Offset doesn 't actually move any cells or change the selection; it just
' * * * returns a reference. OFFSET can be used with any function expecting a
' * * * reference argument.
'
'Example:
' *OFFSET(B11,4,3) equals E15. Using Example 1 below, if you enter this
' * *formula on a worksheet, Microsoft Excel displays the value contained in
' * *cell E15 or the value 9.
'
'================================================= =========================*=
'
'VLOOKUP worksheet function
'Searches for a value in the leftmost column of a table, and then returns a
'value in the same row from a column you specify in the table. Use VLOOKUP
'instead of HLOOKUP when your comparison values are located in a column to
'the left of the data you want to find.
'
'Syntax -
'VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)
'
'Lookup_value * is the value to be found in the first column of the array.
' * * * Lookup_value can be a value, a reference, or a text string.
'
'Table_array * is the table of information in which data is looked up. Use a
' * * * reference to a range or a range name, such as Database or List.
'
' * * * If range_lookup is TRUE, the values in the first column of
table_array must
' * * * be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE,
TRUE;
' * * * otherwise VLOOKUP may not give the correct value. If range_lookup is
FALSE,
' * * * table_array does not need to be sorted.
'
' * * * You can put the values in ascending order by choosing the Sort command
' * * * from the Data menu and selecting Ascending.
'
' * * * The values in the first column of table_array can be text, numbers, or
' * * * logical values.
'
' * * * Uppercase and lowercase text are equivalent.
'
'Col_index_num * is the column number in table_array from which the matching
' * * * value must be returned. A col_index_num of 1 returns the value in the
' * * * first column in table_array; a col_index_num of 2 returns the value in
' * * * the second column in table_array, and so on. If col_index_num is less
' * * * than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is
' * * * greater than the number of columns in table_array, VLOOKUP returns
' * * * the #REF! error value.
'
'Range_lookup * is a logical value that specifies whether you want VLOOKUP
' * * * to find an exact match or an approximate match. If TRUE or omitted,
' * * * an approximate match is returned. In other words, if an exact match is
' * * * not found, the next largest value that is less than lookup_value is
' * * * returned. If FALSE, VLOOKUP will find an exact match. If one is not
' * * * found, the error value #N/A is returned.
'
'Remarks -
'If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
' * * * largest value that is less than or equal to lookup_value.
'
'If lookup_value is smaller than the smallest value in the first column of
' * * * table_array, VLOOKUP returns the #N/A error value.
'
'
'If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
' * * * returns the #N/A value.
'
'Example:
' *VLOOKUP("John",B11:E17,4,FALSE) equals 10. Using Example 1 below, if
' * *you enter this formula on a worksheet, Microsoft Excel displays the
' * *value contained in cell E14 or the value 10.
' * Note that the value 10 is returned instead of 9 or 17. *This is because
' * the FIRST value VLOOKUP finds that matches it's criteria ("John")
' * is returned.
'
'================================================= =========================*=
' *Example 1:
'
' * * * A * * *B * * *C * * *D * * *E
'6 * *John
'7 * *Nov
' 8 * *3
'9
'10
'11 * * * * *Name * Month *Week * Score
'12 * * * * *Joe * * Oct * *1 * * *10
'13 * * * * *Joe * * Nov * *3 * * *15
'14 * * * * *John * *Oct * *2 * * *10
'15 * * * * *John * *Nov * *3 * * * 9
'16 * * * * *John * *Nov * *4 * * *17
'17 * * * * *Pete * *Nov * *4 * * * 8
'18
'19
'20
'
'================================================= =========================*=
'
'Worksheet Formula that uses MULTIPLE CRITERIA to find a value.
'--------------------------------------------------------
'
'Forumla Example 1:
'Using actual values as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")),0)}
' * * * * * * or
'Forumla Example 2:
'Using cell references as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17=A6)*--(C12:C17=A7)),0)}
'
' * * *- Remember to use CTRL-SHIFT-ENTER to make the formula an array
' * * * * * showing the '{' and "}" at the beginning and ending of the formula
'
'Notes about the formula:
' *Important concept - Just as VLOOKUP will give you the FIRST value it finds
' * * * that meets it's criteria, this formul will give you the LAST value it
' * * * finds that meets all of it's criteria.
'
' *E11 - Cell address at top of column of value you are looking for.
' * * * *Ex: (SCORE)
' *ROW(1:6) - # of rows being reviewed.
' * * - in EXAMPLE 1, worksheet rows 12:17 contain the data, therefore,
' * * * * there are 6 rows of data excluding the Column Heading.
' * * * * In this formula, the ROW worksheet function ALWAYS starts with
' * * * * the number 1. *The second number is the count of rows being
' * * * * reviewed exclusive of the Column Headings.
' * * * * Therefore, *ROW(1:6)
' *Double Dash - * Ex: --
' * * * * - we use the -- to coerce the returns of 1 and 0 for
' * * * * * * * True and False values, respectively, in the range being
' * * * * * * * reviewed, otherwise an incorrect result may occur.
' *B12:B17 - Range being reviewed
' * * * *- for the value/text "John" [Forumla Example 1] or
' * * * * *the value in Cell A6 [Forumla Example 2]
' * * * *- If a row has the correct value/text in it, it will evaluate as
' * * * * * * * True. *True = 1
' *C12:C17 - Range being reviewed
' * * * *- for the value/text "Nov" [Forumla Example 1] or
' * * * * *the value in Cell A7 [Forumla Example 2]
' * * * *- If a row has the correct value/text in it, it will evaluate as
' * * * * * * * True. *True = 1
'
' ** - the multiplication causes False results to evaluate to 0
'
'
'================================================= =========================*=
' *Example 2:
' * * * * * * * * * * * * * * * * * * * * ||
' * * * A * * *B * * *C * * *D * * *E * * ||
' 6 * John * * * * * * * * * * * * * * * *|| LOGIC OF FORMULA -
' 7 * Nov * * * * * * * * * * * * * * * * ||
' 8 * *3 * * * * * * * * * * * * * * * * *|| Calculation Results:
True=1/False=0
' 9 * * * * * * * * * * * * * * * * * * * ||
'10 * * * * * * * * * * * * * * * * * * * || * * * * * * * * * * * * * *
Offset *
'11 * * * * *Name * Month *Week * Score * || Offset *John * * Nov * * *John
* Nov
'12 * * * * *Joe * * Oct * *1 * * *10 * * || 1 * * * FALSE * FALSE * * * * 0
'13 * * * * *Joe * * Nov * *3 * * *15 * * || 2 * * * FALSE * TRUE * * * * *0
'14 * * * * *John * *Oct * *2 * * *10 * * || 3 * * * TRUE * *FALSE * * * * 0
'15 * * * * *John * *Nov * *3 * * * 9 * * || 4 * * * TRUE * *TRUE * * * * *4
'16 * * * * *John * *Nov * *4 * * *17 * * || 5 * * * TRUE * *TRUE * * * * *5
'17 * * * * *Pete * *Nov * *4 * * * 8 * * || 6 * * * FALSE * TRUE * * * * *0
'18 * * * * * * * * * * * * * * * * * * * ||
'19 * * * * * * * * * * * * * * * * * * * ||
'20 * * * * * * * * * * * * * * * * * * * ||
'
'================================================= =========================*=
' * * * * * * * * * * * * * * * * * * * * * * * * *
...

read more »

Wow. This is VERY helpful. Thank you so much for this--I really
appreciate the help!