Thanks:  0
Likes:  0

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

1. 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.

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!

2. 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.

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?

3. 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 ]

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•