Formula that output values of all possible permutations

[simones7982]

Hi all -
I have a spreadsheet with raw data in the first tab. All the other tabs work as engines as in they get data from a pair (two rows only) apply some arithmetic formulas and output values. How can I get those engines (tabs) to come up with all output values of all possible pair permutations? See below an explanations of my spreadsheet and of the issue I have in more detail - thank you in advance for your help

In the first tab I have a table with 20 items (listed across 20 rows) and each of those items (listed in column A) has two values one in column B and another one in column C.
Apple, Orange and Pineapple are just 3 of those 20 items.

In the second tab, cell A1 reads one of the 20 items, in this case “apple”, and cell A2 reads another item in this case “orange”.
Cell B1 is the sum of two value retrieved with vlooups functions. The first vlookup retrieves the value in the second column of the item in A1 (apple) and the second vlookup retrieves the value in the third column of the item in A2 (apple).
Cell C1 is the subtraction of two value retrieved with vlooups functions. The first vlookup retrieves the value in the second column of the item in A1 (apple) and the second vlookup retrieves the value in the third column of the item in A2 (apple).

Let’s assume that the value of the first vlookup value is 5 and of the second vlookup is 3. Said so the value in B1 will be 8 and the value in C1 is 3.

In a third tab cell A1 retrieves the value B1 of the second tab. In the same tab value D1 multiplies the value in A1 to a costant number that in this case is 2 and D2 multiplies the value in A1 to a costant number that in this case is 3. This means that the value in D1 is 16 and the value in D2 is 24

In a fourth tab cell A1 retrieves the value C1 of the second tab. In the same tab value D1 multiplies the value in A1 to a costant number that in this case is 4 and D2 multiplies the value in A1 to a costant number that in this case is 5. This means that the value in D1 is 12 and the value in D2 is 15.

In a fifth tab cell D1 and D2 of both the third tab and fourth tab make a 2x2 matrix. Each value is multiplied by another value to output 4 results.
Those 4 results are retrieved in the second tab in A3, A4, A5 and A6.

So far this is all quite simple.

Now I would like to replicate this exercise so to retrieve those 4 values for all other pairs of items of the table in the first tab.
In other words if A1 and A2 reads “apple” and Orange” and now I write in B1 and B2 “apple” and “pineapple”, could you tell what are the functions/formulas that could help B3, B4, B5 and B6 retrieving the output of the same exercise I have done in A3, A4,A5, A6 without creating new tabs or tables but having all the calculations done dynamically?

[#2]

@simones7982   Is it possible to upload a copy of the sheet? You'd be far more likely to get the help you're seeking if you could supplement your very thorough description with the spreadsheet in question.

It's hard to visualize what you're saying, and we'd have to create our own version in order to help....you can greatly facilitate that, and make it more accurate in the process, by showing us what you're working with.

[#3]

@mathetes  You are totally right. It would be easier to try to help me if I share the spreadsheet. 

I have noticed I have made some mistakes when I described the spreadsheet. Please find attached the correct spreadsheet. I have also reformulate the question of what I am trying to achieve in the first tab. 

Thank you for your help 

[#4]

@simones7982

Most of this calculation just melted away.

It turned out that each term of the 2x2 matrix involved the product of calculation 1 and calculation 2 (since they are the sum and difference of two lookups respectively, their product is equivalent to the difference of the squares of the values looked up)

Taking the product out as a factor of the matrix left a constant matrix that applies to every data row.

= Calc1 * Calc2 * Weighting

For me that formula occupies cell E2 and spills to fill the green cells.  

I think implicit intersection will work on legacy systems if the formula is copied to the entire range.

XLOOKUPS would also need to be reverted to VLOOKUPS or INDEX/MATCH. 

[#5]

That's going to take some time, which I don't have right now. I hope there's no rush; or that someone else will feel free to jump in. It's an intriguing problem, I will say. Just need time to contemplate....

[#6]

@Peter Bartholomew 

Hi Peter - Thank you for helping with this. This is much appreciated. 

Will look into the spreadsheet you revised asap and revert with comments. 

[#7]

@simones7982 

This is a stab at producing a workbook that uses (named) relative references rather than dynamic array formulae (it pains me to do it and I cannot guarantee that I have got it right but it seems to be working on my computer).  

By the way, if you are targeting all possible combinations any value1 that equals a value2 will give rise to a row of zeros.