vlookup problem

[Robbyn]

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

 

[Harlan Grove]

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.

 

[daddylonglegs]

Perhaps database only has 217 columns?

 

[Robbyn]

The "database" range:

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

 

[Harlan Grove]

The "database" range:

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$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!.

 

[Robbyn]

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)),"",VLOOKUP($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)),"",VLOOKUP($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?

 

[Tyro]

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]

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]

What is the formula?

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.

 

[Robbyn]

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(Data!$1:$1))

to

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

 

[Dave Peterson]

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.