lookup function 1
 

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

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

Hi!

Take your pick:

A1 = 3
B1 = 1

=VLOOKUP(A1,B2:G7,MATCH(B1,B2:G2,0),0) = C5 = 50

=INDEX(C3:G7,MATCH(A1,B2:B7,0),MATCH(B1,B2:G2,0)) = C5 = 50

I prefer the Vlookup.

Biff


"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

Does your data follow the same pattern? If so, would this work?
(A1=row #, B1=Column #)

=MAX(20*(A1-B1)+10,10)

HTH
--
Dana DeLouis
Win XP & Office 2003


"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

Thanks everybody i'll give all your suggestion a try and get back to you if
i'm successful