Lookup function returning false values

[Luc1fer1022]

Hello!
I am working on excel 2010. I am using the lookup function to pull in employee training dates from one sheet to another sheet.
This is the formula I am using:

=LOOKUP(A10,Sheet1!$A$13:$A$500,Sheet1!$K$13:$K$500)

Where A10 is the employee name in Sheet2, Sheet1 column A is the employee names (sorted alphabetically) and, sheet1 column K is the training date.

The problem is that when I ask it to look up a person's name (in this instance A10) who is not listed on "sheet1" it still returns a seemingly random date rather than a blank or an N/A. If the employees name is in Sheet1 the formula works fine - I spot checked a bunch of them.

On another note, I tried using this function with simply A:A and K:K since the lines above a13 and k13 are blank and it was giving me an error reading.

Thank you for any help you can give!

 

[joeu2004]

This is the formula I am using:
=LOOKUP(A10,Sheet1!$A$13:$A$500,Sheet1!$K$13:$K$500)
[....]
The problem is that when I ask it to look up a person's name (in this instance A10) who is not listed on "sheet1" it still returns a seemingly random date rather than a blank or an N/A.

Because LOOKUP matches the largest value in A13:A500 that is less than or equal to A10. So as long as A10 is not less than A13, a match will be found.

Use VLOOKUP instead. To wit:

=VLOOKUP(A10,Sheet1!$A$13:$K$500,11,FALSE)

FALSE says: look for an exact match. 11 says: return the value in relative column 11; that is column A plus 10, which is column K.

PS.... Improvement: =IFERROR(VLOOKUP(A10,Sheet1!$A$13:$K$500,11,FALSE), ""). Returns the null string ("") if no match.

I tried using this function with simply A:A and K:K

A bad idea, especially with the change that I suggest. That would cause Excel to look at 1+ million rows before failing to find a match.

If your data might extend beyond row 500, choose some larger, but still reasonable limit; for example, $A$13:$K$1000.

 

[Luc1fer1022]

Thank you! The formula worked perfectly.

For my education - since I am new to excel -

How does Sheet1!$A$13:$K$500,11 work the same as Sheet1!$A$13:$A$500,Sheet1!$K$13

I am trying to ask it look for the values in a13:a500 and give me the corresponding value in k13:k500. Is your formula just a more elegant/simple way to write it? Would mine work as well? Or is there something wrong with the way I wrote it?


Also, is there a way to replace the #N/A error with a value for example could I make it say "not present" instead of the error message?

 

[joeu2004]

For my education - since I am new to excel -
How does Sheet1!$A$13:$K$500,11 work the same as Sheet1!$A$13:$A$500,Sheet1!$K$13:$K$500 [errata]

As I explained: "11 says: return the value in relative column 11; that is column A plus 10, which is column K".

So if A10 matches A100, VLOOKUP returns K100, "the corresponding value in k13:k500" as you require.

Also, is there a way to replace the #N/A error with a value for example could I make it say "not present" instead of the error message?

See the "PS" that I added to my previous response after my initial posting. So if you looked at only my original posting (in email?), you might have overlooked it.

IFERROR was added in Excel 2007. Let me know if you are using a previous version of Excel or if you are saving the file as "xls", which is limited to pre-XL2007 functions.

PS.... Instead of IFERROR(..., ""), you might want IFERROR, "not present").

 

[joeu2004]

Caveat lector: Always look for after-posting edits. Sigh, I added some again, which you might overlook.

 

[Luc1fer1022]

Got it! Thank you!
And thanks for the heads up on the PS. Duly noted!