I'm having a similar problem. I have 2 tables. One tables is of products
and options. The data of this table are the part #s for the product with
that option. The second table is of part #s and their prices. I'm trying to
SUM all VLOOKUPs of all products for a particular option. In the example
below, I can't seem to get the TOTAL line working in the first table. My
current formula for the total of Option1 column is:
=SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE))

==VIEW THIS IN FIXED TEXT==

For example:
Product Option1 Option2
A 1 2
B 3 4
C 5 6
TOTAL $45 $60

Part Price
1 $5
2 $10
3 $15
4 $20
5 $25
6 $30


"Crazy Pete" wrote:

Within the attached array formula, I need to either perform a lookup, or use
the data I have per iteration, to count for various values in column B.
However, as I understand, VLOOKUP requires a single value or reference as the
lookup_value. I have tried using the INDEX function (see below), but it
appears to only pick up row 1 for each iteration, hence not matching with any
valid lookup values.

How do I, inside an array calculation, specify the row which has been found
to match the preceding IF statements?

Effectively, I wish to perform a lookup on another workbook, using column C
and the row which has been found to match the two preceding IF statements.

=SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0))

regards,
Pete.