T
Tom Davis
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")),D265536,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.
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")),D265536,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.