In Sheet A I have something like the following:
| Apples | Oranges | Grapes | Bananas |
|---|---|---|---|
| 15% | 20% | 25% | 40% |
| 228 | 304 | 380 | 608 |
In Sheet B I have the following:
| Price | Apples | Oranges | Grapes | Bananas |
|---|---|---|---|---|
| $0.15 | formula | formula | formula | formula |
| $0.72 | formula | formula | formula | formula |
In Sheet B, where I have written "formula" above, I want to have a formula that takes the value in the "Price" column for that row and multiples it by the corresponding percentage value for the header from Sheet A. For example, in the first "formula" in Sheet B under Apples I would have =A2*('SheetA' vLookup 'Apples' A2:Z2)
In this particular example I would expect the result to be $0.15*0.15=0.0225. Using the same formula, Sheet B should result with:
| Price | Apples | Oranges | Grapes | Bananas |
|---|---|---|---|---|
| $0.15 | 0.0225 | 0.03 | 0.0375 | 0.06 |
| $0.72 | 0.108 | 0.144 | 0.18 | 0.288 |
Edit for additional detail: The formula must search the header in the first sheet range because the positions in Sheet A are not static and may change. So for example, today the "Apples" percent may be Sheet A, B2 and tomorrow it may be in Sheet A, D2. It will always be in the same row (2), but not always in the same column. It will always have the same header in both sheets, so as long as it finds "Apples" in Sheet A, the next cell in row 2 will be the correct percent.
Try using the following in SheetB!B2:
=HLOOKUP(B$1, SheetA!$A$1:$2, 2, FALSE)*$A2
You'll actually want to use HLOOKUP instead of VLOOKUP, since you're searching a row horizontally instead of a column. You can then drag/copy the formula to all the relevant cells on SheetB.