I am developing a template to do calculations against a large set of data (36
columns and 65,000+ rows) that I will be receiving every month. I have set up
my workbook with 2 worksheets for data (55,000 and 10,000 lines) and one
worksheet each for a Summary and Pivot Tables. On the Summary worksheet I am
trying to enter a formula that will take a value from cell G12 and match that
value to a cell in column AJ of the designated data worksheet ONLY if it
matches 2 criteria found in columns A and B of that same worksheet and have
it display the contents of the same line in column D.

Here is one of the formulas that I have tried without success, entered as an
array formula, CTRL-SHFT-ENTER.
=LOOKUP(G12,'CG-FFG Data'!AJ2:AJ655369,(IF(A2:AJ65536="CG47,IF('CG-FFG
Data'!B2:B65536="52")),D2:D65536,FALSE))
In this case it brings back an error and highlights the "52". I have tried
taking out the quotes but the result is the same.

The Summary Worksheet looks like this:
Col A Col B Col F Col G Col H
Ship (Calculated Cells) *A (Result of Lookup)
CG52
CG53
CG54
etc
*A = Lookup Value resulting from a MIN formula

The CG-FFG Data worksheet looks like this:
Col A Col B Col C Col D Col AJ
Class Hull (Hide) Task Metric Value
CG47 52 I-77222 -0.648
CG47 52 I-44151 0.738
CG47 53 Q-12311 0.840
CG47 53 I-51473 -1.956
CG47 54 I-11021 -1.644
CG47 55 I-23311 0.497
CG47 56 I-24312 0.627
DDG51 51 Q-24325 -0.648
DDG51 52 I-48299 1.000
DDG51 53 I-58311 0.545
DDG51 54 I-34311 -0.684
etc.
etc.

If the formula works correctly it will bring back I-77222 for CG52 not the
Q-24325 which does not meet the CG47 and hull 52 criteria. There is always an
exact match to the value being looked up since it is known to exist by virtue
of the fact that it was found by the MIN formula for col AJ for CG47+52.

Any help you can provide will be greatly appreciated.

--
Tom Davis
"Getting Smarter Every Day"
(One formula at a time!)

Try the below array formula.. G2 will have the min value. Change the cell
references to suit

=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100="CG47")*
($B$2:$B$100=52)*($E$2:$E$100=G2),0))

If this post helps click Yes
---------------
Jacob Skaria


"Tom Davis" wrote:

I am developing a template to do calculations against a large set of data (36
columns and 65,000+ rows) that I will be receiving every month. I have set up
my workbook with 2 worksheets for data (55,000 and 10,000 lines) and one
worksheet each for a Summary and Pivot Tables. On the Summary worksheet I am
trying to enter a formula that will take a value from cell G12 and match that
value to a cell in column AJ of the designated data worksheet ONLY if it
matches 2 criteria found in columns A and B of that same worksheet and have
it display the contents of the same line in column D.

Here is one of the formulas that I have tried without success, entered as an
array formula, CTRL-SHFT-ENTER.
=LOOKUP(G12,'CG-FFG Data'!AJ2:AJ655369,(IF(A2:AJ65536="CG47,IF('CG-FFG
Data'!B2:B65536="52")),D2:D65536,FALSE))
In this case it brings back an error and highlights the "52". I have tried
taking out the quotes but the result is the same.

The Summary Worksheet looks like this:
Col A Col B Col F Col G Col H
Ship (Calculated Cells) *A (Result of Lookup)
CG52
CG53
CG54
etc
*A = Lookup Value resulting from a MIN formula

The CG-FFG Data worksheet looks like this:
Col A Col B Col C Col D Col AJ
Class Hull (Hide) Task Metric Value
CG47 52 I-77222 -0.648
CG47 52 I-44151 0.738
CG47 53 Q-12311 0.840
CG47 53 I-51473 -1.956
CG47 54 I-11021 -1.644
CG47 55 I-23311 0.497
CG47 56 I-24312 0.627
DDG51 51 Q-24325 -0.648
DDG51 52 I-48299 1.000
DDG51 53 I-58311 0.545
DDG51 54 I-34311 -0.684
etc.
etc.

If the formula works correctly it will bring back I-77222 for CG52 not the
Q-24325 which does not meet the CG47 and hull 52 criteria. There is always an
exact match to the value being looked up since it is known to exist by virtue
of the fact that it was found by the MIN formula for col AJ for CG47+52.

Any help you can provide will be greatly appreciated.

--
Tom Davis
"Getting Smarter Every Day"
(One formula at a time!)

=LOOKUP(G12,'CG-FFG Data'!AJ2:AJ655369,(IF(A2:AJ65536="CG47,IF('CG-FFG
Data'!B2:B65536="52")),D2:D65536,FALSE))

Try this array formula** :

=INDEX('CG-FFG Data'!D2:D65536,MATCH(1,IF('CG-FFG
Data'!A2:A65536="CG47",IF('CG-FFG Data'!B2:B65536=52,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Tom Davis" wrote in message
...
I am developing a template to do calculations against a large set of data
(36
columns and 65,000+ rows) that I will be receiving every month. I have set
up
my workbook with 2 worksheets for data (55,000 and 10,000 lines) and one
worksheet each for a Summary and Pivot Tables. On the Summary worksheet I
am
trying to enter a formula that will take a value from cell G12 and match
that
value to a cell in column AJ of the designated data worksheet ONLY if it
matches 2 criteria found in columns A and B of that same worksheet and
have
it display the contents of the same line in column D.

Here is one of the formulas that I have tried without success, entered as
an
array formula, CTRL-SHFT-ENTER.
=LOOKUP(G12,'CG-FFG Data'!AJ2:AJ655369,(IF(A2:AJ65536="CG47,IF('CG-FFG
Data'!B2:B65536="52")),D2:D65536,FALSE))
In this case it brings back an error and highlights the "52". I have tried
taking out the quotes but the result is the same.

The Summary Worksheet looks like this:
Col A Col B Col F Col G Col H
Ship (Calculated Cells) *A (Result of Lookup)
CG52
CG53
CG54
etc
*A = Lookup Value resulting from a MIN formula

The CG-FFG Data worksheet looks like this:
Col A Col B Col C Col D Col AJ
Class Hull (Hide) Task Metric Value
CG47 52 I-77222 -0.648
CG47 52 I-44151 0.738
CG47 53 Q-12311 0.840
CG47 53 I-51473 -1.956
CG47 54 I-11021 -1.644
CG47 55 I-23311 0.497
CG47 56 I-24312 0.627
DDG51 51 Q-24325 -0.648
DDG51 52 I-48299 1.000
DDG51 53 I-58311 0.545
DDG51 54 I-34311 -0.684
etc.
etc.

If the formula works correctly it will bring back I-77222 for CG52 not the
Q-24325 which does not meet the CG47 and hull 52 criteria. There is always
an
exact match to the value being looked up since it is known to exist by
virtue
of the fact that it was found by the MIN formula for col AJ for CG47+52.

Any help you can provide will be greatly appreciated.

--
Tom Davis
"Getting Smarter Every Day"
(One formula at a time!)

Jacob,

You were right on the money. Yes has been clicked.

Thanks,

Tom Davis
--
Tom Davis
"Getting Smarter Every Day"
(One formula at a time!)


"Jacob Skaria" wrote:

Try the below array formula.. G2 will have the min value. Change the cell
references to suit

=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100="CG47")*
($B$2:$B$100=52)*($E$2:$E$100=G2),0))

If this post helps click Yes
---------------
Jacob Skaria


"Tom Davis" wrote:

I am developing a template to do calculations against a large set of data (36
columns and 65,000+ rows) that I will be receiving every month. I have set up
my workbook with 2 worksheets for data (55,000 and 10,000 lines) and one
worksheet each for a Summary and Pivot Tables. On the Summary worksheet I am
trying to enter a formula that will take a value from cell G12 and match that
value to a cell in column AJ of the designated data worksheet ONLY if it
matches 2 criteria found in columns A and B of that same worksheet and have
it display the contents of the same line in column D.

Here is one of the formulas that I have tried without success, entered as an
array formula, CTRL-SHFT-ENTER.
=LOOKUP(G12,'CG-FFG Data'!AJ2:AJ655369,(IF(A2:AJ65536="CG47,IF('CG-FFG
Data'!B2:B65536="52")),D2:D65536,FALSE))
In this case it brings back an error and highlights the "52". I have tried
taking out the quotes but the result is the same.

The Summary Worksheet looks like this:
Col A Col B Col F Col G Col H
Ship (Calculated Cells) *A (Result of Lookup)
CG52
CG53
CG54
etc
*A = Lookup Value resulting from a MIN formula

The CG-FFG Data worksheet looks like this:
Col A Col B Col C Col D Col AJ
Class Hull (Hide) Task Metric Value
CG47 52 I-77222 -0.648
CG47 52 I-44151 0.738
CG47 53 Q-12311 0.840
CG47 53 I-51473 -1.956
CG47 54 I-11021 -1.644
CG47 55 I-23311 0.497
CG47 56 I-24312 0.627
DDG51 51 Q-24325 -0.648
DDG51 52 I-48299 1.000
DDG51 53 I-58311 0.545
DDG51 54 I-34311 -0.684
etc.
etc.

If the formula works correctly it will bring back I-77222 for CG52 not the
Q-24325 which does not meet the CG47 and hull 52 criteria. There is always an
exact match to the value being looked up since it is known to exist by virtue
of the fact that it was found by the MIN formula for col AJ for CG47+52.

Any help you can provide will be greatly appreciated.

--
Tom Davis
"Getting Smarter Every Day"
(One formula at a time!)