Hi all,

I posted the following in application errors, but I think I posted it in the
wrong. Hopefully, this is the right group. :)

Why does the following formula work:

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

But this one doesn't (I get a #REF error):

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

"Robbyn" wrote...
....
Why does the following formula work:

=IF(ISBLANK(VLOOKUP($B$1,Database,217,0)),"",
VLOOKUP($B$1,Database,217,0))

But this one doesn't (I get a #REF error):

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

This is what would happen if the range named Database spanned only 217
columns.

Perhaps database only has 217 columns?

"Robbyn" wrote:

Hi all,

I posted the following in application errors, but I think I posted it in the
wrong. Hopefully, this is the right group. :)

Why does the following formula work:

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

But this one doesn't (I get a #REF error):

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

The "database" range:

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

"daddylonglegs" wrote:

Perhaps database only has 217 columns?

"Robbyn" wrote:

Hi all,

I posted the following in application errors, but I think I posted it in the
wrong. Hopefully, this is the right group. :)

Why does the following formula work:

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

But this one doesn't (I get a #REF error):

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

"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!.

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!.

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!.