Bernie,
Thanks for the reply.
I was trying to see how I could solve the problem without having to write a
custom function...

I can achieve something really close using VLOOKUP

=VLOOKUP($B3,Scheme,$C$1+1)*$A3
or even
=VLOOKUP($B3,Scheme,Column()-2)*$A3"
I guess I should be happy with that. I still might try to parse an array
somehow.

Regards
HS

-----------Worksheet setup ---

First row and first column are excel row and column headings
_|a,b,c,d,e <-- Ignore this row
1|,,1,2,3
2|BasePrice,Scheme,Price1,Price2,Price3
3|1,D,13,23,33
4|1,B,11,21,31

Scheme (named Range)
A,10,20,30,40
B,11,21,31,41
C,12,22,32,42
D,13,23,33,43
E,14,24,34,44
F,15,25,35,45
G16,26,36,46
H,17,27,37,47

Range("C3").Formula = "=VLOOKUP($B3,Scheme,$C$1+1)*$A3"
Range("C4").Formula = "=VLOOKUP($B3,Scheme,$D$1+1)*$A3"
Range("C5").Formula = "=VLOOKUP($B3,Scheme,$E$1+1)*$A3"