# Replace #N/A In Lookup With Custom Text

• Hello,

I have a quick question on looking up a value in a table.

So I have this value on one worksheet: Example 1

Now, I have a table on the second worksheet with a bunch of id's in list of 1-100. Now, each product has one of those id's and also has a product name.

So, right now, I am trying to figure out what the product name would be if my id specified is a valid id in the table on worksheet 2. Now, if the specified id isn't a valid id in the table (for example 105) I need the cell the say, "No Product".

Here is my if function: =IF(what do I put here,VLOOKUP(\$A\$1,Table1,2), "Product Not Found")

So what do I put for the logical test to find out if the id is a valid id in the table that i'm looking up the id's.

The bottom line: If you get an error like #Value! or #N/A, or any of those types of errors, how can you rename the cell so say something else instead of that.

-3xpl0it

• Re: Lookup Value In Table

There are various ways you can do this. This might not be the best solution, but I'm having a crack at helping others in this forum where I usually get so much help

You can build it all into one formula like:

Cheers,
Averil

-------------
[FONT="Comic Sans MS"]Averil Pretty[/FONT]

• Re: Lookup Value In Table

That works great! Thank you very much!
:):):):):):):):)
-3xpl0it

• Re: Lookup Value In Table

-------------
[FONT="Comic Sans MS"]Averil Pretty[/FONT]

• Re: Lookup Value In Table

Hello,

Sorry, but I have 1 more question. If the current cell had a #value! error, what would I write in my if statement to say, you have an error?

Right now, I am trying this, but no luck: =IF(ISERROR(TRUE),"You have an error", (A1*A2)+A3)

• Re: Lookup Value In Table

When are you getting the #value! error? Maybe if you could provide an example I could have a look...

-------------
[FONT="Comic Sans MS"]Averil Pretty[/FONT]

• Re: Lookup Value In Table

Actually, just got it! I just started my formula with IFERROR. Thank you very much for your help today!

-3xpl0it

• Re: Lookup Value In Table

No worries mate Glad you got it

There are lots of helpful pages on here including this page Dave has put together that explains common errors and how to handle them:

http://www.ozgrid.com/Excel/formula-errors.htm

Cheers,
Averil

-------------
[FONT="Comic Sans MS"]Averil Pretty[/FONT]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!