That is because it is an array formula, so you commit it with
Ctrl-Shift-Enter, not just Enter.

Actually, to be of any use to you to replicate for other letters, the table
should be absolute. When you do this, you will see the formula surrounded by
{...} in the formula bar. Excel inserts these, you do not need to type them
in.

=MATCH(J1,LEFT($B$2:$B$200),0)+1

--
HTH

Bob Phillips

"Bking" wrote in message
...
Thanks Biff.

The first formula works. The second formula returned #Value!.

I created a column for the first formula. When I change the value in J1,
the first row in that column with a value 0 is the letter in J1.

Now if I can only figure out how to incorporate one of these nto a macro.


"Biff" wrote:

Hi!

Doesn't help with wanting a macro but here's a formula that will do what
you
want:

Assume last names are in the range B2:B200

In a cell, say, J1 enter the letter of the last name that you want to
find.

Either one of these formulas entered as an array using the key combo of
CTRL,SHIFt,ENTER:

=MIN(IF(LEFT(B2:B200)=J1,ROW(2:200)))

=MATCH(J1,LEFT(B2:B200),0)+1

Biff

"Bking" wrote in message
...
I have a large worksheet contains rwos with name and address columns.
it
is
sorted in ascending order by the last name column and address. I want
to
print a list of the names and addresses with page breaks at specific
letters.
How can I locate the first row with a last name beginning with a
specific
letter so that I can insert a page break? I know I can do this
manually
by
scrolling throught the list, finding the right row, selecting it and
inserting a page break. However, I would like to create a macro to
insert
all the page breaks automatically.