Thank you for your great replies. I have Excel 2013 so unfortunately the array formulas don't work (but maybe it's for some other reason, I have also different language settings but even if I translated the function names, nothing happened) but I think that Eric's suggestion about intermediate values is great. Maybe it will be easier to do this:
1. Assign numbers 1 to 7 to the notes of the scale (C to B in C major).
2. List the notes of the target scale (G, A, B, C, D, E, F, G in G major). I'm omitting the sharps/flats for now.
3. Ascertain the interval between the scales: perfect fifth.
4. Work out number correspondences. Like this:
C - 1
D - 2
E - 3
F - 4
G - 5
A - 6
B - 7
C must become G, so 1 > 5. D must become A and so on. We get: 1 > 5, 2 > 6, 3 > 7, 4 > 1, 5 > 2, 6 > 3, 7 > 4.
If the interval btw the source scale and target scale is different, these correspondences will shift. So you just need to work out 5 more (you have 7 notes which means 6 possibilities of transposition, again leaving out sharps/flats for which I think an independent second step could be invented, that would be easier. What do you think? I hope what I've outlined is not just restating your suggestions (I don't see into your formulas).