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.

    Thanks in advance,

  • 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:

    =IF(ISNA(VLOOKUP(A1,Table1,2,FALSE)),"Product not found",VLOOKUP(A1,Table1,2,FALSE))


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

  • Re: Lookup Value In Table


    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]

Participate now!

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