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


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


    Cheers,
    Averil

    -------------
    [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]

Participate now!

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