VLOOKUP is very limited

B

Bobby

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

Jezebel

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

Bobby

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
 
D

Dave Peterson

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

Bobby

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
 
P

Peo Sjoblom

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
 
B

Bobby

Peo Sjoblom said:
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
Click to expand...
 
Top