vlookup error

[Guest]

I have a spreadsheet that has employees badge numbers in the first column,
employee names in the second column and employee department numbers in the
third column. I have a vlookup formula in another workbook that links to this
spreadsheet, when you type the employee's badge number it is supposed to
automatically bring up the employee's name in the cell in the next column.

Most of the time it works just fine, but every once in awhile it comes up
with the incorrect employee's name. Sometimes it is four or five rows above
the correct employee's name (and the badge numbers aren't even close to being
the same). The spreadsheet is alphabetized by employee name.

I looked online and saw that when using the vlookup function you have to
make sure that cells with numbers in them aren't formatted as text. I made
sure that all of the cells with numbers are formatted as numbers. But I don't
know if this will help or not, since the problem seems to happen randomly.

To fix the problem I just re-do the formula and then it seems to work fine,
but not everyone using the forms knows how to do this and some of the forms
are protected.

Hopefully this makes sense. Thank you for your help.

 

[Guest]

the only thing I can think of that might cause the VLOOKUP's to work at one
time and not at another is possible some trouble with the linking, and maybe
sometimes they will be actually looking in to another file to get the results.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Roger Govier]

Hi

You say that your List is sorted alphabetically, but that does not mean
that your Badge numbers are in sorted order.
My guess is that you are not using the optional 4th argument in your
Vlookup formula of False or 0 which is needed if the list isn't sorted.

=VLOOKUP(ID-No,Range,2,0)

--
Regards

Roger Govier


"Vlookup not accurate" <Vlookup not (e-mail address removed)>
wrote in message
news:[email protected]...

 

[Dave Peterson]

My first guess is that you don't have calculation set for automatic:
tools|Options|calculation tab

 

[Guest]

Thank you for your help. I am using false for the 4th argument. Should I be
using 0 instead? That would bring the result to zero if the vlookup couldn't
find the exact badge number match (is that right?).

 

[Guest]

Thank you for your help. I checked my settings and they are set to automatic.
Something really strange is going on here.

 

[Dave Peterson]

What does "re-do the formula" mean?

If all you do is hit F2, followed by enter, then I don't have any more guesses.

You may want to post the formula you're using.

(and false and 0 are equivalent as that 4th argument)

 

[Guest]

I basically just retype the formula. Here is what it looks like:

=VLOOKUP($C6,'[Names List.xls]Sheet1'!$A:$J,7,FALSE)

Thanks again for your help.

 

[Dave Peterson]

Just a guess.

If your formula reevalates with a simple F2, followed by an enter, maybe excel
needs to be awakened (a very technical term!).

I'd select all the cells and do this:
edit|replace
what: = (just an equal sign)
with: = (that same equal sign)
replace all

Excel will see that each formula is changing (for its purposes--not yours) and
recalc.

I basically just retype the formula. Here is what it looks like:

=VLOOKUP($C6,'[Names List.xls]Sheet1'!$A:$J,7,FALSE)

Thanks again for your help.

 

[Guest]

thank you. I will give that a try.

Just a guess.

If your formula reevalates with a simple F2, followed by an enter, maybe excel
needs to be awakened (a very technical term!).

I'd select all the cells and do this:
edit|replace
what: = (just an equal sign)
with: = (that same equal sign)
replace all

Excel will see that each formula is changing (for its purposes--not yours) and
recalc.

I basically just retype the formula. Here is what it looks like:

=VLOOKUP($C6,'[Names List.xls]Sheet1'!$A:$J,7,FALSE)

Thanks again for your help.

What does "re-do the formula" mean?

If all you do is hit F2, followed by enter, then I don't have any more guesses.

You may want to post the formula you're using.

(and false and 0 are equivalent as that 4th argument)

Vlookup not accurate wrote:

Thank you for your help. I checked my settings and they are set to automatic.
Something really strange is going on here.

:

My first guess is that you don't have calculation set for automatic:
tools|Options|calculation tab



Vlookup not accurate wrote:

I have a spreadsheet that has employees badge numbers in the first column,
employee names in the second column and employee department numbers in the
third column. I have a vlookup formula in another workbook that links to this
spreadsheet, when you type the employee's badge number it is supposed to
automatically bring up the employee's name in the cell in the next column.

Most of the time it works just fine, but every once in awhile it comes up
with the incorrect employee's name. Sometimes it is four or five rows above
the correct employee's name (and the badge numbers aren't even close to being
the same). The spreadsheet is alphabetized by employee name.

I looked online and saw that when using the vlookup function you have to
make sure that cells with numbers in them aren't formatted as text. I made
sure that all of the cells with numbers are formatted as numbers. But I don't
know if this will help or not, since the problem seems to happen randomly.

To fix the problem I just re-do the formula and then it seems to work fine,
but not everyone using the forms knows how to do this and some of the forms
are protected.

Hopefully this makes sense. Thank you for your help.