| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup problem
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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
"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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
"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!. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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!. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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!. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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!. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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!. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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!. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup problem
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| VLOOKUP problem | Excel Worksheet Functions | |||
| VLookup Problem | Excel Discussion (Misc queries) | |||
| vlookup problem | Excel Worksheet Functions | |||
| VLOOKUP problem | Excel Worksheet Functions | |||
Linear Mode
