excel formula

[Guest]

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

 

[JE McGimpsey]

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))

 

[Myrna Larson]

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))

 

[Myrna Larson]

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.

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))

work?

 

[Guest]

Thank you, your fix worked perfectly.

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))

 

[Myrna Larson]

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.

 

[meena khan via OfficeKB.com]

Hi dear !

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



thanks

Tanha

 

[meena khan via OfficeKB.com]

hi dear

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




thaNks

khan

 

[Max]

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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

work?

 

[Kassie Kasselman via OfficeKB.com]

Hey Tanha

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