ExcelBanter - Lookup Data in two seperate Spreadsheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Lookup Data in two seperate Spreadsheets (https://www.excelbanter.com/excel-worksheet-functions/96256-lookup-data-two-seperate-spreadsheets.html)

Padraig

Lookup Data in two seperate Spreadsheets

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

Lookup Data in two seperate Spreadsheets

'
'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 ||
'
'================================================= ==========================
' 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

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

Lookup Data in two seperate Spreadsheets

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,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 ||
'
'================================================= ==========================
' 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

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

Lookup Data in two seperate Spreadsheets

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,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 ||
'
'================================================= ==========================
' 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

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

Lookup Data in two seperate Spreadsheets

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,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 ||
'
'================================================= ==========================
' 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

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

Lookup Data in two seperate Spreadsheets

Padraig

Lookup Data in two seperate Spreadsheets

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

All times are GMT +1. The time now is 05:13 PM.