Results 1 to 7 of 7
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Reverse MATCH (Excel2003/2007)

    Hi

    The MATCH function will return a pointer to the first found entry in a range.

    Is there a simple way to find the LAST matching entry in a range?

    For example, in a block of cells (say 20 columns by 50 rows) each cell contains either A, B or C or is blank.
    For each row, I want a formula to find the last column that contains B


    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse MATCH (Excel2003/2007)

    See INDEX & SMALL - Particular Occurrence - "last occurrence" is treated at the end of the article.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Reverse MATCH (Excel2003/2007)

    Hi Hans

    Many thanks for that link.
    Very interesting.

    I decided to use an array formula:
    {=MAX(((a6:bx6)="B")*$a$5:$bx$5))

    I set values of 1 to 50 in row 5 i.e. in cells $a$5:$bx$5
    Then for row6, this array formula will return the corresponding number for the last entry of "B"

    I can then copy the formula for the other rows.

    Many thanks again

    zeddy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse MATCH (Excel2003/2007)

    You can use the COLUMN function instead of entering 1, 2, ..., 50 in A5:BX5, in the following array formula (Ctrl+Shift+Enter):

    =MAX((A6:BX6="B")*COLUMN(A6:BX6))

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Reverse MATCH (Excel2003/2007)

    Hans

    Brilliant!
    Even better.

    Although I've used the ROW function I keep forgetting about its sister function COLUMN.

    zeddy

  6. #6
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse MATCH (Excel2003/2007)

    This form handles a range that starts after column A.

    {=MAX((myRange="B")*COLUMN(myRange)) - (COLUMN(myRange)-1)}

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Reverse MATCH (Excel2003/2007)

    Thanks Mike

    Always useful to have flexibility!

    zeddy

Posting Permissions

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