1. ## 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. ## Re: Reverse MATCH (Excel2003/2007)

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

3. ## Re: Reverse MATCH (Excel2003/2007)

Hi Hans

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. ## 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. ## 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. ## Re: Reverse MATCH (Excel2003/2007)

This form handles a range that starts after column A.

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

7. ## 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
•