Lookup Data in two seperate Spreadsheets

Padraig · 06-27-2006, 07:10 AM

Hello,

I have two spreadsheets one with multiple dates and multiple client and
trade values (column A is Client code, Column b is date and Column G is the
result I need) and the other with single dates and single client values
(Column Cis the client code, Column D is the date). I want to extract the
trade values from the first spreadsheet based on a combination of a matched
client/date value. How can I do this? I think it may be a vlookup with other
functions embedded.

Many thanks,

Craig

Gary L Brown · 06-27-2006, 01:20 PM

'
'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,range_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 ||
'
'===========================================================================
' or
' Largest (Max) Row offset => 5 x 1 x 1 = 5
'
' Offset of 5 rows from E11 = E15 = 17
'
' Note that the value 17 is returned instead of 10 or 9. This is because
' the LAST/HIGHEST ROW/MAX value the formula finds that matches it's
criteria
' ("John" and "Nov") is returned.
'----------------------------------------
'Note: This method can easily be adapted to use 3, 4 or more criteria
'
'Forumla Example 2 [will return value of 9]:
'Using actual values as the search criteria in EXAMPLE 1:
'{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")*--(D12:D17=3)),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)*--(D12:D17=A8)),0)}
'
' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
' showing the '{' and "}" at the beginning and ending of the formula
'
'
'
'
'
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"Padraig" wrote:

> Hello,
>
> I have two spreadsheets one with multiple dates and multiple client and
> trade values (column A is Client code, Column b is date and Column G is the
> result I need) and the other with single dates and single client values
> (Column Cis the client code, Column D is the date). I want to extract the
> trade values from the first spreadsheet based on a combination of a matched
> client/date value. How can I do this? I think it may be a vlookup with other
> functions embedded.
>
> Many thanks,
>
> Craig

Padraig · 06-28-2006, 04:10 AM

Thanks Gary,

A can you give me a more specific example based on the criteria I have
mentioned, the Offset function says it only returns a reference not a value.

Many thanks,

Craig

"Gary L Brown" wrote:

> '
> '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,range_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 ||
> '
> '===========================================================================
> ' or
> ' Largest (Max) Row offset => 5 x 1 x 1 = 5
> '
> ' Offset of 5 rows from E11 = E15 = 17
> '
> ' Note that the value 17 is returned instead of 10 or 9. This is because
> ' the LAST/HIGHEST ROW/MAX value the formula finds that matches it's
> criteria
> ' ("John" and "Nov") is returned.
> '----------------------------------------
> 'Note: This method can easily be adapted to use 3, 4 or more criteria
> '
> 'Forumla Example 2 [will return value of 9]:
> 'Using actual values as the search criteria in EXAMPLE 1:
> '{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")*--(D12:D17=3)),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)*--(D12:D17=A8)),0)}
> '
> ' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
> ' showing the '{' and "}" at the beginning and ending of the formula
> '
> '
> '
> '
> '
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "Padraig" wrote:
>
> > Hello,
> >
> > I have two spreadsheets one with multiple dates and multiple client and
> > trade values (column A is Client code, Column b is date and Column G is the
> > result I need) and the other with single dates and single client values
> > (Column Cis the client code, Column D is the date). I want to extract the
> > trade values from the first spreadsheet based on a combination of a matched
> > client/date value. How can I do this? I think it may be a vlookup with other
> > functions embedded.
> >
> > Many thanks,
> >
> > Craig

Padraig · 06-28-2006, 04:30 AM

I've got around the mulitple cell values by using "& to combine the two,
however I am now getting #N/A in cells that do not return a value, how do I
get rid of these?

Many thanks,

Craig

"Padraig" wrote:

> Thanks Gary,
>
> A can you give me a more specific example based on the criteria I have
> mentioned, the Offset function says it only returns a reference not a value.
>
> Many thanks,
>
> Craig
>
> "Gary L Brown" wrote:
>
> > '
> > '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,range_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 ||
> > '
> > '===========================================================================
> > ' or
> > ' Largest (Max) Row offset => 5 x 1 x 1 = 5
> > '
> > ' Offset of 5 rows from E11 = E15 = 17
> > '
> > ' Note that the value 17 is returned instead of 10 or 9. This is because
> > ' the LAST/HIGHEST ROW/MAX value the formula finds that matches it's
> > criteria
> > ' ("John" and "Nov") is returned.
> > '----------------------------------------
> > 'Note: This method can easily be adapted to use 3, 4 or more criteria
> > '
> > 'Forumla Example 2 [will return value of 9]:
> > 'Using actual values as the search criteria in EXAMPLE 1:
> > '{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")*--(D12:D17=3)),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)*--(D12:D17=A8)),0)}
> > '
> > ' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
> > ' showing the '{' and "}" at the beginning and ending of the formula
> > '
> > '
> > '
> > '
> > '
> > HTH,
> > --
> > Gary Brown
> > gary_brown@ge_NOSPAM.com
> > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > Post Helpfull to you?''.
> >
> >
> > "Padraig" wrote:
> >
> > > Hello,
> > >
> > > I have two spreadsheets one with multiple dates and multiple client and
> > > trade values (column A is Client code, Column b is date and Column G is the
> > > result I need) and the other with single dates and single client values
> > > (Column Cis the client code, Column D is the date). I want to extract the
> > > trade values from the first spreadsheet based on a combination of a matched
> > > client/date value. How can I do this? I think it may be a vlookup with other
> > > functions embedded.
> > >
> > > Many thanks,
> > >
> > > Craig

Padraig · 06-28-2006, 04:35 AM

I have got around this issue by using "&" to combine the two cells in both
spreadsheets to give a unique value. However now when I use Vlookup I get
~N/A for any fields that have no value to return, how can I get around this?

Many thanks,

Craig

"Padraig" wrote:

> Thanks Gary,
>
> A can you give me a more specific example based on the criteria I have
> mentioned, the Offset function says it only returns a reference not a value.
>
> Many thanks,
>
> Craig
>
> "Gary L Brown" wrote:
>
> > '
> > '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,range_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 ||
> > '
> > '===========================================================================
> > ' or
> > ' Largest (Max) Row offset => 5 x 1 x 1 = 5
> > '
> > ' Offset of 5 rows from E11 = E15 = 17
> > '
> > ' Note that the value 17 is returned instead of 10 or 9. This is because
> > ' the LAST/HIGHEST ROW/MAX value the formula finds that matches it's
> > criteria
> > ' ("John" and "Nov") is returned.
> > '----------------------------------------
> > 'Note: This method can easily be adapted to use 3, 4 or more criteria
> > '
> > 'Forumla Example 2 [will return value of 9]:
> > 'Using actual values as the search criteria in EXAMPLE 1:
> > '{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")*--(D12:D17=3)),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)*--(D12:D17=A8)),0)}
> > '
> > ' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
> > ' showing the '{' and "}" at the beginning and ending of the formula
> > '
> > '
> > '
> > '
> > '
> > HTH,
> > --
> > Gary Brown
> > gary_brown@ge_NOSPAM.com
> > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > Post Helpfull to you?''.
> >
> >
> > "Padraig" wrote:
> >
> > > Hello,
> > >
> > > I have two spreadsheets one with multiple dates and multiple client and
> > > trade values (column A is Client code, Column b is date and Column G is the
> > > result I need) and the other with single dates and single client values
> > > (Column Cis the client code, Column D is the date). I want to extract the
> > > trade values from the first spreadsheet based on a combination of a matched
> > > client/date value. How can I do this? I think it may be a vlookup with other
> > > functions embedded.
> > >
> > > Many thanks,
> > >
> > > Craig

Gary Brown · 06-28-2006, 09:30 AM

Craig,
After re-reading what you asked, it occured to me that maybe you wanted
the SUM of all Trade Values for a Client/Date combination.
If that's the case, the ARRAY formula below is an example of what you're
looking for.

{=SUM((MultiDate!$A$2:$A$19=SingleDate!C2)*(MultiDate!$B$2:$B$19=SingleDate!D2)*(MultiDate!$G$2:$G$19))}

You'll notice the '{ }'s. You don't type them. Instead of hitting ENTER
when you've finished the formula, you hit CTRL-SHIFT-ENTER.

Take a look at Chip Pearson's site concerning arrays to more fully
understand the formula above. His example of...
=SUM((A2:A10="Phone")*(B2:B10="Smith")*C2:C10)
is comparable to the formula I showed you above.

http://www.cpearson.com/excel/array.htm

HTH,
--
Gary Brown
[email protected]
If this post was helpful to you, please select 'YES' at the bottom of the
post.

"Padraig" wrote:

> I have got around this issue by using "&" to combine the two cells in both
> spreadsheets to give a unique value. However now when I use Vlookup I get
> ~N/A for any fields that have no value to return, how can I get around this?
>
> Many thanks,
>
> Craig
>
> "Padraig" wrote:
>
> > Thanks Gary,
> >
> > A can you give me a more specific example based on the criteria I have
> > mentioned, the Offset function says it only returns a reference not a value.
> >
> > Many thanks,
> >
> > Craig
> >
> > "Gary L Brown" wrote:
> >
> > > '
> > > '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,range_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 ||
> > > '
> > > '===========================================================================
> > > ' or
> > > ' Largest (Max) Row offset => 5 x 1 x 1 = 5
> > > '
> > > ' Offset of 5 rows from E11 = E15 = 17
> > > '
> > > ' Note that the value 17 is returned instead of 10 or 9. This is because
> > > ' the LAST/HIGHEST ROW/MAX value the formula finds that matches it's
> > > criteria
> > > ' ("John" and "Nov") is returned.
> > > '----------------------------------------
> > > 'Note: This method can easily be adapted to use 3, 4 or more criteria
> > > '
> > > 'Forumla Example 2 [will return value of 9]:
> > > 'Using actual values as the search criteria in EXAMPLE 1:
> > > '{=OFFSET(E11,MAX(ROW(1:6)*--(B12:B17="John")*--(C12:C17="Nov")*--(D12:D17=3)),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)*--(D12:D17=A8)),0)}
> > > '
> > > ' - Remember to use CTRL-SHIFT-ENTER to make the formula an array
> > > ' showing the '{' and "}" at the beginning and ending of the formula
> > > '
> > > '
> > > '
> > > '
> > > '
> > > HTH,
> > > --
> > > Gary Brown
> > > gary_brown@ge_NOSPAM.com
> > > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > > Post Helpfull to you?''.
> > >
> > >
> > > "Padraig" wrote:
> > >
> > > > Hello,
> > > >
> > > > I have two spreadsheets one with multiple dates and multiple client and
> > > > trade values (column A is Client code, Column b is date and Column G is the
> > > > result I need) and the other with single dates and single client values
> > > > (Column Cis the client code, Column D is the date). I want to extract the
> > > > trade values from the first spreadsheet based on a combination of a matched
> > > > client/date value. How can I do this? I think it may be a vlookup with other
> > > > functions embedded.
> > > >
> > > > Many thanks,
> > > >
> > > > Craig

Padraig · 06-28-2006, 10:10 AM

Many thanks Gary.

"Padraig" wrote:

> Hello,
>
> I have two spreadsheets one with multiple dates and multiple client and
> trade values (column A is Client code, Column b is date and Column G is the
> result I need) and the other with single dates and single client values
> (Column Cis the client code, Column D is the date). I want to extract the
> trade values from the first spreadsheet based on a combination of a matched
> client/date value. How can I do this? I think it may be a vlookup with other
> functions embedded.
>
> Many thanks,
>
> Craig