Need help with a MATCH function (maybe INDEX?!?)

[mach3]

let's say i have an array in A1:A1000 named ARRAY1.

let's say the word 'Total' appears approximately every 100 rows in ARRAY1.

if i use =MATCH("Total", ARRAY1,0), it'll return 100 because the word 'Total' first appears on Line 100.

how do i find the 1st instance of 'Total' in ARRAY1 after Line 200? or Line 300?

in other words, i don't want the 1st instance of 'Total' in the array...i want the 1st instance of 'Total' after a certain placemarker in the array.

thanks in advance!

mach3


NOTES:

stipulation: i MUST use ARRAY1 in the formula.

should i be using an INDEX function?

also, if i know what row to start looking in the array (e.g., row 200, row 300), can i use some kind of INDIRECT function in the MATCH formula and thus bypass the use of the named ARRAY1? for example, can i do a =MATCH("Total", INDIRECT(Sheet1!A200:A1000),0) or something like that? thanks!

[Aladin Akyurek]

On 2002-05-08 13:51, mach3 wrote:
let's say i have an array in A1:A1000 named ARRAY1.

let's say the word 'Total' appears approximately every 100 rows in ARRAY1.

if i use =MATCH("Total", ARRAY1,0), it'll return 100 because the word 'Total' first appears on Line 100.

how do i find the 1st instance of 'Total' in ARRAY1 after Line 200? or Line 300?

in other words, i don't want the 1st instance of 'Total' in the array...i want the 1st instance of 'Total' after a certain placemarker in the array.

thanks in advance!

mach3


NOTES:

stipulation: i MUST use ARRAY1 in the formula.

should i be using an INDEX function?

also, if i know what row to start looking in the array (e.g., row 200, row 300), can i use some kind of INDIRECT function in the MATCH formula and thus bypass the use of the named ARRAY1? for example, can i do a =MATCH("Total", INDIRECT(Sheet1!A200:A1000),0) or something like that? thanks!

But you already know where the nth instance of "Total" is. So, What is the purpose of using MATCH?

[donh]

highlight the entire range

use data/filter/auto filter

then select the drop down and customize, contains, total

this will show you every row that has "total" in it

[ This Message was edited by: donh on 2002-05-08 14:19 ]

[ This Message was edited by: donh on 2002-05-08 14:19 ]