Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP is very limited
Has anyone else had problems with VLOOKUP if there are more than 250 rows the
VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#2
|
|||
|
|||
VLOOKUP is very limited
Check that your data is properly sorted, and your range correctly defined.
There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#3
|
|||
|
|||
VLOOKUP is very limited
The Data is a bunch of Last Names in one Column and First names in the next
and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#4
|
|||
|
|||
VLOOKUP is very limited
If you're using =vlookup() and trying to match on the last name, then my bet is
the first match for that last name is missing the first name. If you put this in B1 =A1 and leave A1 empty, you'll see 0. Same with excel. You may want to post the formula you're using and what you're trying to do. Something like: =vlookup(a1,sheet2!a:b,2,false) may help though. Bobby wrote: The Data is a bunch of Last Names in one Column and First names in the next and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. -- Dave Peterson |
#5
|
|||
|
|||
VLOOKUP is very limited
Here is what I wrote
=VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5) D18 is a validation pulldown(list) of Employee Names listed in 'Employee Info' A-column. 5 is the column I'm trying to extract the information from that gives me 000000 'Employee Info'!$A$4:$E$503 is the employee information with a table sort executed with the Last names as the left column from column C instead of A. Looking at where I'm getting the error, it shows that the sort actually keeps everything in alphabetized. Column A is a consolidated (LAST Name First Name) list of 300+ employees. I did this by combining a column of Last name and a column of first names via, =C4& " "&D4 = (SMITH John) C-column being Last Names D-column being First Names Kind of a strange way of extracting info, I know. Thanks for the suggestings. Bobby "Bobby" wrote: The Data is a bunch of Last Names in one Column and First names in the next and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#6
|
|||
|
|||
VLOOKUP is very limited
You should use exact match in your formula
=VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,0) or =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,FALSE) that way you'll get an error when excel can't find the lookup value -- Regards, Peo Sjoblom "Bobby" wrote in message ... Here is what I wrote =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5) D18 is a validation pulldown(list) of Employee Names listed in 'Employee Info' A-column. 5 is the column I'm trying to extract the information from that gives me 000000 'Employee Info'!$A$4:$E$503 is the employee information with a table sort executed with the Last names as the left column from column C instead of A. Looking at where I'm getting the error, it shows that the sort actually keeps everything in alphabetized. Column A is a consolidated (LAST Name First Name) list of 300+ employees. I did this by combining a column of Last name and a column of first names via, =C4& " "&D4 = (SMITH John) C-column being Last Names D-column being First Names Kind of a strange way of extracting info, I know. Thanks for the suggestings. Bobby "Bobby" wrote: The Data is a bunch of Last Names in one Column and First names in the next and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#7
|
|||
|
|||
VLOOKUP is very limited
"Peo Sjoblom" wrote: You should use exact match in your formula =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,0) or =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,FALSE) that way you'll get an error when excel can't find the lookup value -- Regards, Peo Sjoblom "Bobby" wrote in message ... Here is what I wrote =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5) D18 is a validation pulldown(list) of Employee Names listed in 'Employee Info' A-column. 5 is the column I'm trying to extract the information from that gives me 000000 'Employee Info'!$A$4:$E$503 is the employee information with a table sort executed with the Last names as the left column from column C instead of A. Looking at where I'm getting the error, it shows that the sort actually keeps everything in alphabetized. Column A is a consolidated (LAST Name First Name) list of 300+ employees. I did this by combining a column of Last name and a column of first names via, =C4& " "&D4 = (SMITH John) C-column being Last Names D-column being First Names Kind of a strange way of extracting info, I know. Thanks for the suggestings. Bobby "Bobby" wrote: The Data is a bunch of Last Names in one Column and First names in the next and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
#8
|
|||
|
|||
VLOOKUP is very limited
Thank-you very much, problem solved! Thank everyone for you input!!!
Bobby Yotsuuye "Peo Sjoblom" wrote: You should use exact match in your formula =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,0) or =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5,FALSE) that way you'll get an error when excel can't find the lookup value -- Regards, Peo Sjoblom "Bobby" wrote in message ... Here is what I wrote =VLOOKUP(D18,'Employee Info'!$A$4:$E$503,5) D18 is a validation pulldown(list) of Employee Names listed in 'Employee Info' A-column. 5 is the column I'm trying to extract the information from that gives me 000000 'Employee Info'!$A$4:$E$503 is the employee information with a table sort executed with the Last names as the left column from column C instead of A. Looking at where I'm getting the error, it shows that the sort actually keeps everything in alphabetized. Column A is a consolidated (LAST Name First Name) list of 300+ employees. I did this by combining a column of Last name and a column of first names via, =C4& " "&D4 = (SMITH John) C-column being Last Names D-column being First Names Kind of a strange way of extracting info, I know. Thanks for the suggestings. Bobby "Bobby" wrote: The Data is a bunch of Last Names in one Column and First names in the next and I've sorted the data properly. The Range also includes the entire table to include a bunch of empty rows towards the end. Do you think that this may be the problem? Thanks for the response. YoYo "Jezebel" wrote: Check that your data is properly sorted, and your range correctly defined. There isn't an inherent limit: the function works as expected on correct data, to the full limit of the spreadsheet. "Bobby" wrote in message ... Has anyone else had problems with VLOOKUP if there are more than 250 rows the VLOOKUP value defaults to 00000 instead of returning a value from the column in which it is supposed to extract from. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |