+ Reply to Thread
Results 1 to 12 of 12

Vlookup Producing Error Codes

  1. 02-16-2015, 09:20 PM #1
    Bedmunds
    Bedmunds is offline
    Registered User
    Join Date
    02-16-2015
    Location
    Arizona
    MS-Off Ver
    2013
    Posts
    7

    Vlookup Producing Error Codes

    I have recently had the pleasure of having my formula problem resolved in this forum. I now have a new one that I am hopeful is just as easy to resolve. I have a vlookup formula that begins with the input of a number (1-733) in cell A28 in worksheet 1 pulling information from worksheet 3 (Material Pricing). When there is no number input in A28l (just blank) it errors the formula to show #NA in the corresponding cell.

    How do I get that to remain a blank cell? I have tried an =ISNA and =ISERROR formula with no avail. Any thoughts?


    Her is my current formula: =VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0)
    Reply With QuoteRegister To Reply
  2. 02-16-2015, 09:26 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup Producing Error Codes

    Maybe this...

    =IFERROR(VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0),"")

    Or this...

    =IF($A28="","",VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 02-16-2015, 09:27 PM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Vlookup Producing Error Codes

    Did you try adding =iferror(your formula),0) to it? Out of curiosity, since you only count over one column why does your formula go over six columns (A:F)?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 02-16-2015, 09:27 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Vlookup Producing Error Codes

    I thought I answered this in your other thread ...
    Perhaps wrap your formula in IFERROR....
    =IFERROR(VLOOKUP($A2,Sheet3!$A:$F,2,0),"")
    http://www.excelforum.com/excel-form...p-formula.html
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 02-16-2015, 09:40 PM #5
    Bedmunds
    Bedmunds is offline
    Registered User
    Join Date
    02-16-2015
    Location
    Arizona
    MS-Off Ver
    2013
    Posts
    7

    Re: Vlookup Producing Error Codes

    I have tried both formulas and both return the same results - #NA in the cell. Any other thoughts?
    Reply With QuoteRegister To Reply
  6. 02-16-2015, 09:41 PM #6
    Bedmunds
    Bedmunds is offline
    Registered User
    Join Date
    02-16-2015
    Location
    Arizona
    MS-Off Ver
    2013
    Posts
    7

    Re: Vlookup Producing Error Codes

    Thank you for the reply - you did respond and I tried your suggestion prior to posting the new thread as it did not work. Do you have any other ideas?
    Reply With QuoteRegister To Reply
  7. 02-16-2015, 09:43 PM #7
    Bedmunds
    Bedmunds is offline
    Registered User
    Join Date
    02-16-2015
    Location
    Arizona
    MS-Off Ver
    2013
    Posts
    7

    Re: Vlookup Producing Error Codes

    Thank you - I did try that with no luck. My formula is pulling information out of 6 columns in worksheet 3 off a code that is input in worksheet 1. Does that help?
    Reply With QuoteRegister To Reply
  8. 02-16-2015, 09:50 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Vlookup Producing Error Codes

    OK, either you did not use them properly, or there is something else going on there.

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    Reply With QuoteRegister To Reply
  9. 02-16-2015, 09:54 PM #9
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Vlookup Producing Error Codes

    Ok give this a try, =iF(VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0),="","",VLOOKUP ($a28,'material pricing '!$a:$f,2,0)) might work.

    Edit, done on iPad, not tested.
    Reply With QuoteRegister To Reply
  10. 02-16-2015, 10:25 PM #10
    Bedmunds
    Bedmunds is offline
    Registered User
    Join Date
    02-16-2015
    Location
    Arizona
    MS-Off Ver
    2013
    Posts
    7

    Re: Vlookup Producing Error Codes

    Here is what it looks like with the formulas in place. The formulas work great (Thank you by the way). A user inputs a code (found in worksheet 3 and inputs it to A28 which in turn populates the row of coorisponding data in the adjacent columns.

    ITEM DESCRIPTION/STYLE Style QTY SIZE PER UNIT COST TOTAL UNIT COST

    #N/A #N/A #N/A #N/A #VALUE!

    #N/A #N/A #N/A #N/A #VALUE!

    #N/A #N/A #N/A #N/A #VALUE!

    #N/A #N/A #N/A #N/A #VALUE!

    #N/A #N/A #N/A #N/A #VALUE!

    #N/A #N/A #N/A #N/A #VALUE!



    After it is populate it should look like this:


    ITEM DESCRIPTION/STYLE Style QTY SIZE PER UNIT COST TOTAL UNIT COST

    1 EMT EMT Conduit 1/2" $13.68 #VALUE!

    499 90 Degree Connector Sealtite Whip 6' 3/4" $3.95 #VALUE!

    444 90 Degree Conector G.F 6' Whip 1/2" $0.75 #VALUE!

    34 Conn SS EMT Conduit 1 1/2" $1.25 #VALUE!

    42 Conn SS EMT Conduit 2" $1.61 #VALUE!

    Does this help?
    Reply With QuoteRegister To Reply
  11. 02-16-2015, 10:46 PM #11
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Vlookup Producing Error Codes

    to upload your problem file.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    you can delete other data.

    All the solution provided should all fix your issue then not. there could be something. maybe the layout.

    And with a sample this could be fixed in a matter of minutes.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  12. 02-17-2015, 07:40 AM #12
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Vlookup Producing Error Codes

    so, maybe give this a try...
    =IF(IFERROR(VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0),"")="","",VLOOKUP($A28,'MATERIAL PRICING'!$A:$F,2,0))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Syntax producing an error
    By paradise2sr in forum Excel General
    Replies: 7
    Last Post: 05-25-2014, 12:13 PM
  2. loop that keeps producing a next without for error
    By Sc0tt1e in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 03-26-2014, 04:49 AM
  3. [SOLVED] Nest LEFT in VLOOKUP producing error
    By msawyer in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-07-2012, 07:26 PM
  4. [SOLVED] VLOOKUP producing #N/A
    By brooksbrah in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2012, 04:28 PM
  5. Formatting Cells Problem producing error in my vlookup's
    By donnydorko in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-08-2012, 08:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1