Index, Match, Lookup, Vlookup or Combination?

[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 E >> Col AE Col AF>>Col AJ AJ
Class Hull (Hide) Task (Misc Data) (Calc Values)
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.

I would provide a copy of the template but with data it runs over 75 megs.

Any help you can provide will be greatly appreciated.

 

[Pecoflyer]

HOW TO GET FURTHER HELP WITH A WORKBOO
For further help with it why not join our forums (shown i
the link below) it's completely free, if you do join *you will have th
opportunity to add attachments to your posts so you can add workbooks t
better illustrate your problems and get help directly with them.* Als
if you do join please post in this thread (link found below) so tha
people who have been following or helping with this query can continu
to do so. :

--
Pecoflye

Cheers -

Firefox 3.5.3 is as slow as possible