**EDIT:** A recent answer from @ScottCraner used the "de-referenced" `INDEX()`

formula and his answer made me decide to take another crack at this problem. The same approach I had (unsuccessfully) tried before worked perfectly the second time around. I'll describe the solution below.

**Background about the De-referenced INDEX() formula:**

Sean, you've made an admirable attempt to use array formulas to do what you need to do. The problems you're having are related to the way Excel handles arrays. Some formulas can use arrays as arguments and some can't.

I did some digging into this and I learned some very bizarre, arcane things about using arrays in Excel's `INDEX()`

formula that I didn't know before. To understand how this formula works, let's start at the end.

The very last thing that your formula would do is sum three (discontinuous) values from the 2D array that is your Table 2.

`INDEX(array,row_num,col_num)`

can return a single value from a 2D array, and it can also return a whole column or row. It seems like it *ought* to be able to return a list of values. So let's test it.

This formula would (in a perfect world) return the sum you're looking for from Table 2:

`=SUM(INDEX(G4:K8,{3,2,3},{5,4,3}))`

That should add the elements from row 3,column 5 plus row 2, column 4 plus row 3, column 3. But **it doesn't**, it just returns 1.67 which is the first element referenced.

Searching online produces references (including one here on StackOverflow) that say `INDEX()`

will return an array, but only if you **de-reference** the formula (that's the "bizarre" part). The "arcane" part is how to do that. This is the "de-referenced" formula:

`=SUM(INDEX(G4:K8,N(IF(1,{3,2,3})),N(IF(1,{5,4,3}))))`

This formula gives the correct answer: 4.67.

In the formula, the `IF()`

treats the 1 as `True`

, so it returns the array of numbers, and the `N()`

returns the array of numbers if they are numbers, which they are. Why the IF() and N() are required to make the formula work correctly is anybody's guess. In Scott's formula, he had to also multiply his array (it was a range reference) by 1.

But, now we have a formula that gives the right answer. And hopefully, all we have to do is replace the array constants with calculated arrays using your other data.

**New information starts here.**

For the row_num's in the formula above `{3,2,3}`

, we need the positions of the percent intensities in F4:F8 associated with the chosen fruit varieties. First, we'll get an array of the positions of the Apples in `G12:G16`

of your Table 3:

`=MATCH(B3:B5,G12:G16,0)`

This is an array formula and must be entered with `CTRL``Shift``Enter`, rather than just `Enter`.

This formula looks for the list of Apple varieties from Table 1 in Column G of Table 3 and returns an array of their positions.

If you select the formula in the formula bar and hit F9, you'll see the *value* of the formula is the array `{1,3,4}`

, the positions of the Apples in Column G of Table 3.

Now we need the PI's associated with those positions. This `INDEX()`

formula looks in Column H and uses the above array as the row_num's. Here, the row_num's have to be "de-referenced":

`=INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0))))`

This formula returns the array `{0.97,0.98,0.97}`

, the PI's of the Apples. So far, so good. Next we use that array as the lookup values in a `MATCH()`

formula that looks in F4:F8, the PI index of your Table 2:

`=MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0)`

This formula returns the array `{3,2,3}`

, and those are the row_num's needed for the final formula.

Next we need the col_num's `{5,4,3}`

, which are the total number of fruits for each of the Apple varieties. We'll get this from Table 3, but first we need to calculate the total number of fruits for all of the fruit varieties. This (calculated) array is a list of those totals:

`(I12:I16*J12:J16)+K12:K16`

To get the total number of fruits for the Apple varieties, we'll use that array in an `INDEX()`

, with the same (de-referenced) row_num's as before:

`=INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))`

This formula returns the array `{5,4,3}`

, and those are the col_num's needed for the final formula.

Putting this all together, the list of NFPI's is:

`=INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))`

This formula returns the array `{1.67;2;1}`

. Those are the NFPI's for Apples, and now we just have to add them up.

But not quite yet, there's a minor issue to take care of first. All three of the Apple varieties can be found in Table 3, but this is not true for Oranges. The formulas above return arrays with `#N/A`

in them where the Small Orange variety can't be found. This doesn't cause any problems until it comes time to add up the values.

So before taking the sum, we convert the `#N/A's`

to 0 with an `IFERROR()`

formula. Here is the final formula:

`=SUM(IFERROR(INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(C3:C5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(C3:C5,G12:G16,0))))),0))`

This formula returns 4.67 for the Apples and 5.75 for the Oranges.

Sean, I hope this can still be useful. Sorry for the long delay.