If you put these in a couple of unused cells in a temporary worksheet

=counta(data!$a:$a)
and
=counta(data!$1:$1)

what values are returned?

It could explain why your formula didn't work.

Robbyn wrote:

As a followup,

For some reason I had to redefine my range with a finite number of columns
instead of dynamic . After I did that, the vlookup worked. No clue why...

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1))

to

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),225)

"Tyro" wrote:

What is the formula?

"Robbyn" wrote in message
...
The only thing I changed was which column I wanted the formula to
reference.
That's it. The column has data and its part of the range, "Database".
I'm
missing something somewhere and its driving me nuts.

"Tyro" wrote:

Excel is only a computer program. If you change the size of something and
do
not tell Excel, it has no idea what you did.

"Robbyn" wrote in message
...
Hi Harlan,

Thank you for your reply. I guess I'm trying not to be too obtuse. I
thought that my named range, "Database", didn't limit the number of
columns,
but built rows as we added data. There is data in the column the
formula
is
referencing, but I get the ref error.
The formula
=IF(ISBLANK(VLOOKUP($B$1,Database,217,0)),"",VLOOK UP($B$1,Database,217,0))
returns the correct data from column HI on my data sheet, but if I plug
the
same data in HJ and use the formula

=IF(ISBLANK(VLOOKUP($B$1,Database,218,0)),"",VLOOK UP($B$1,Database,218,0))

I get the ref error

I'm wondering if vlookup limits the number of columns? Or am I being
obtuse?

"Harlan Grove" wrote:

"Robbyn" wrote...
The "database" range:

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),COUNTA(D ata!$1:$1))
....

That's nice.

So what do COUNTA(Data!$A:$A) and COUNTA(Data!$1:$1) evaluate to?

The only ways VLOOKUP returns #REF! errors is when its 3rd argument
evaluates to a number 1 + number of columns spanned by its 2nd
argument
OR
when the cell in the row matching its 1st argument in the column given
by
its 3rd argument just happens to evaluate to #REF!.










--

Dave Peterson