If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

add a column to the left and use this code. Just change the cell info to
match the columns u are trying to match.

=if(a1=b2, "AAA", " ")

Phxlatinoboi®

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

If you are using the VLOOKUP or HLOOKUP functions you can add the
"False" condition which will return an error value if there isn't an
exact match. Unfortunately, this isn't available for LOOKUP. However,
you could use an IF statement like the following to accomplish the same
result.
A1: The value you want to find
B1:B10 The range to search
C1:C10 The range of the result to return

=IF(A1<LOOKUP(A1,B1:B10,B1:B10),"Not Found",LOOKUP(A1,B1:B10,C1:C10))

Hope that helps.

- John
www.JohnMichl.com

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

Thanks, but I'm not sure where this column is supposed to go.
This is my formula: =LOOKUP(L104,'COMMISSION
PERCENTAGES'!$A$1:$A$26,'COMMISSION PERCENTAGES'!$B$1:$B$26)
It looks on the second sheet (named Commission percentages) to find the part
number I've entered and return the commission percentage for that part. I
need it to tell me if the part isn't listed, otherwise it uses the percentage
for the closest part number and I pay the wrong commission.
Hopefully I'm making sense.



"Phxlatinoboi®" wrote:

add a column to the left and use this code. Just change the cell info to
match the columns u are trying to match.

=if(a1=b2, "AAA", " ")

Phxlatinoboi®

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

It works, it works, it works. Wish I had posted this question before I spent
so much time on it. Thank you very, very much.

"Kleev" wrote:

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

You can even get fancier if you want to see a message instead of #NA...

=IF(ISNA(VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)),"Not
Found",VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE))

- if the vLookup can't find the value and returns an #NA, 'Not Found' is
shown instead, otherwise it returns the value.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Kim Greenlaw" wrote:

It works, it works, it works. Wish I had posted this question before I spent
so much time on it. Thank you very, very much.

"Kleev" wrote:

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

I like it! Maybe not for this spreadsheet, but as I change over my old
FoxBase programs to Excel and Access, I can see being able to display
different messages coming in very handy.
Thanks!

"Gary L Brown" wrote:

You can even get fancier if you want to see a message instead of #NA...

=IF(ISNA(VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)),"Not
Found",VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE))

- if the vLookup can't find the value and returns an #NA, 'Not Found' is
shown instead, otherwise it returns the value.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Kim Greenlaw" wrote:

It works, it works, it works. Wish I had posted this question before I spent
so much time on it. Thank you very, very much.

"Kleev" wrote:

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

add an extra argument on the end of your vlookup. ie
=(vlookupD10,A2:C10,2,False)

the False on the end makes excel search for an exact match only




"Kim Greenlaw" <Kim wrote in message
...
If lookup doesn't find a match in the first column, can I get it to return
an
"ERR" or "0", something that would let me know there was no match? Right
now
it returns the value in the second column of the closest value.

Glad it worked for you. I realized after I posted my response that you had
actually asked about lookup rather than vlookup.

"Kim Greenlaw" wrote:

It works, it works, it works. Wish I had posted this question before I spent
so much time on it. Thank you very, very much.

"Kleev" wrote:

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

Hi,
I think that was part of my problem. I should have been using vlookup. If
I could ask one more question. I understand the formula, lookup what I put
in this cell, find it in column a of this range and tell me what is in column
b on the same line. Give me #N/A if it's not there. But what does the "2"
refer to?

"Kleev" wrote:

Glad it worked for you. I realized after I posted my response that you had
actually asked about lookup rather than vlookup.

"Kim Greenlaw" wrote:

It works, it works, it works. Wish I had posted this question before I spent
so much time on it. Thank you very, very much.

"Kleev" wrote:

use the false argument:
=VLOOKUP(A14, Sheet4!$A$66:$B$70, 2, FALSE)
if no match, it will return #N/A

"Kim Greenlaw" wrote:

If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.

VLOOKUP ( a , b , c, d)

a = what to lookup; can be a hard coded value or a reference to another
cell
b = where to look for it; this is a range of multiple columns but the
lookup is in the first column
c = the column number of "b" that has the value that should be
returned. In your case, column 2
d = False indicator requires exact matches.

Hope that helps.

- John Michl

It refers to the second column of the range used in the Vlookup
formula. In this case you are only using $A$66:$B$70, but if this was a
table of, say, 5 columns then your range would be $A$66:$E$70, and the
"2" could be 3, 4 or 5 to return data from the appropriate column.

The FALSE argument (could be 0 instead) means you are only interested
in an exact match.

Hope this helps.

Pete