+ Reply to Thread
Results 1 to 8 of 8

#n/a - vlookup

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    38

    #n/a - vlookup

    I have this formula: =VLOOKUP(I7,A1:F33,6,FALSE)*I6

    And I would like it to show $00.00 instead of #N/A

    I googled it an can't seem to find anything that applies.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,705

    Re: #n/a - vlookup

    =iferror(VLOOKUP(I7,A1:F33,6,FALSE)*I6,0) and format as currency.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: #n/a - vlookup

    maybe
    =IFERROR(VLOOKUP(I7,A1:F33,6,FALSE)*I6,0)
    formated as currency

  4. #4
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    38

    Re: #n/a - vlookup

    This one too: =I8*IF(OR(I5>12,I9=0),0,IF(I9=1,0.1,IF(I9=2,0.25,IF(I9=3,0.55,IF(I9=4,0.7,1)*IF(I9=5,1,1)))))

  5. #5
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    38

    Re: #n/a - vlookup

    You're awesome, that worked.

  6. #6
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    606

    Re: #n/a - vlookup

    That last part of your formula *IF(I9=5,1,1) is irrelevant.
    It says that if I9=5 then 1 and if not then 1..so no matter what is in I9 it multiplies the existing number *1...therefore does nothing.

    This shorter formula might work for you...but it will only handle 0-5. If there is a 6 it will fail.
    =IF(I5>12,0,I8*CHOOSE(I9+1,0,0.1,0.25,0.55,0.7,1))

    Otherwise, your existing formula could be:
    =I8*IF(OR(I5>12,I9=0),0,IF(I9=1,0.1,IF(I9=2,0.25,IF(I9=3,0.55,IF(I9=4,0.7,1)))))

    or

    =IFERROR(IF(I5>12,0,I8*CHOOSE(I9+1,0,0.1,0.25,0.55,0.7,1)),1)

  7. #7
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    38

    Re: #n/a - vlookup

    I had the same formula you are recommending and when I try entering a value of 5 in I9, it returned #N/A so I added that one in. It seems to be working just fine with what I have in there.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,705

    Re: #n/a - vlookup

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  2. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  3. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  4. [SOLVED] Vlookup is not working and giving =vlookup(B2,$T$2:$U$135,2,false) this kind of values.
    By yogeshsharma1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2013, 04:08 PM
  5. how to bring all vlookup returns even with duplicate vlookup search keys
    By NYC4LIFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 04:53 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Replies: 5
    Last Post: 07-29-2009, 07:53 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1