| keyword | value | keyword | value |
|---|---|---|---|
| Apple | 6% | Apples | 2.21% |
| Apples | 5% | Mango | 8.40% |
| Mango | 2.10% | Orange | 9.50% |
| Orange | 3.40% | Apple | 3.10% |
I have an example sheet here. I need to first look for the row that contain the exact keyword values. Subtract Value 1 and Value 2 (Value 1 - Value 2).
For example:
Look for Apple (row 2 for first pair, row 5 for second pair) Subtract the values: 6% - 3.10% = 2.9%
Look for Apples (row 3 for first pair, row 2 for second pair) Subtract the values: 5% - 2.21% = 2.79%
Look for Mango (row 4 for first pair, row 3 for second pair) Subtract the values: 2.10% - 8.40% = -6.3%
Look for Orange (row 5 for first pair, row 4 for second pair) Subtract the values: 3.40% - 9.50% = -6.1%
I tried using Vlookup and sumif but I only get errors. Here's a sample sheet 
CodePudding user response：
use:
=INDEX(QUERY({A2:B; C2:C, D2:D*-1},
"select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"))
