Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,074
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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?

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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