Reverse MATCH (Excel2003/2007)

Topic

New Reply

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

Reply | Quote

Replies

See INDEX & SMALL – Particular Occurrence – “last occurrence” is treated at the end of the article.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hans

Brilliant!
Even better.

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

zeddy

Reply | Quote

This form handles a range that starts after column A.

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

Reply | Quote

Thanks Mike

Always useful to have flexibility!

zeddy

Reply | Quote