You can use the INDEX() function to do this. How do you determine the ROW
#? I came up with this, but it needs refinement:

=MATCH(B5,C2:G2,0)
Gives you the "column number" where there is a match for B5 in the array
C2:G2.

=INDEX(C2:G7,3,MATCH(B5,C2:G2,0))



"Colboyfx" wrote in message
...
a1 b c d e f g
2 1 2 3 4 5
3 1 10 10 10 10 10
4 2 30 10 10 10 10
5 3 50 30 10 10 10
6 4 70 50 30 10 10
7 5 90 70 50 30 10

hope someone can help me please, i'm trying to build a function that will
allow me to lookup a value based on the row/column ref returned from the
results of another formula. e.g lookup value in col b (3) then lookup
value
in row 2 (1) return value where the two results intersect (C5=50), i know
how
to create name ranges but can't work out how to create a function that
will
return the result i'm looking for

thanks in advance