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