excel formula
 

How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to work?

I would use an OFFSET formula.

If I read your formula correctly, the lookup-range is $A$x:$J$x where the row
number, 'x', is contained in B17:

=MATCH(C2,OFFSET($A$1,B17+3,0,10,1))

On Sun, 10 Apr 2005 08:31:02 -0700, "John777"
wrote:

How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to work?

Another formula that will work is

=MATCH(C2,INDEX($A$1:$J$1000,B17+4,0))

Make the number 1000 large enough to encompass the last possible row. When you
specify the column number as 0, the formula returns the entire row.

On Sun, 10 Apr 2005 11:59:36 -0500, Myrna Larson
wrote:

I would use an OFFSET formula.

If I read your formula correctly, the lookup-range is $A$x:$J$x where the row
number, 'x', is contained in B17:

=MATCH(C2,OFFSET($A$1,B17+3,0,10,1))

On Sun, 10 Apr 2005 08:31:02 -0700, "John777"
wrote:

How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to
work?

Thank you, your fix worked perfectly.
"JE McGimpsey" wrote:

one way:

=MATCH(C2,INDIRECT(ADDRESS(B17+4,1,4,1) & ":"
&ADDRESS(B17+4,10,4,1)))

Although it would be much more efficient to use:

=MATCH(C2,OFFSET($A$1,B17+3,0,1,10))




In article ,
"John777" wrote:

How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to work?

To do this with ADDRESS, you need to embed the ADDRESS function inside and
INDIRECT function, like this:

=MATCH(C2,INDIRECT(ADDRESS(B17+4,1,4,1)):INDIRECT( ADDRESS(B17+4,10,4,1)))

To repeat, the other two options are OFFSET and INDEX,

=MATCH(C2,OFFSET($A$1,B17+3,0,10,1))
=MATCH(C2,INDEX($A$1:$J$1000,B17+4,0))

both of which are shorter and probably faster to recalculate. INDEX may be
more understandable.

wrote:
How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to
work?

Hi dear !

i need ExceL formula if you help Me tHeN pLz send Me tHe Excel formulas ok
.......



thanks

Tanha

--
Message posted via http://www.officekb.com

hi dear

i need Grade formula and persontage if any one have plz seNd me




thaNks

khan

--
Message posted via http://www.officekb.com

Try: =MATCH(C2,INDIRECT("A"&B17+4&":J"&B17+4),0)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John777" wrote in message
...
How do I get "=MATCH(C2,ADDRESS(B17+4,1,4,1):ADDRESS(B17+4,10,4 ,1))" to
work?

Hey Tanha

I suggest you start a new thread, and specify what it is that you are
looking for

--
Message posted via http://www.officekb.com