+ Reply to Thread
Results 1 to 4 of 4

need VLOOKUP to generate a zero if match not found

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    62

    need VLOOKUP to generate a zero if match not found

    Hi guys & gals,

    Need help with formula for vlookup to generate a zero if a match is not found in the first column. My formula looks for a matching date in B1, and if it finds it does a vlookup. This is for part inventory so I need a zero returned if the part is not on the list in col A because only parts with a positive inventory show up in the report I have to use.

    Here is the current formula: =IF(BL5=Inventory!$B$1,VLOOKUP($C7,Inventory!$A3:$C72,2),BK10)

    Thanks in Advance!
    Last edited by merlyn45; 05-11-2012 at 01:09 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: need VLOOKUP to generate a zero if match not found

    Try using IFERROR like this

    =IF(BL5=Inventory!$B$1,IFERROR(VLOOKUP($C7,Inventory!$A3:$C72,2,FALSE),0),BK10)

    Note: I also added FALSE to your VLOOKUP otherwise you won't necessarily get an "exact match"
    Audere est facere

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: need VLOOKUP to generate a zero if match not found

    If you do not find a match the wrap it in =IFERROR(value,value_if_error)

    value_if_error >> set this to 0
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: need VLOOKUP to generate a zero if match not found

    Thanks guys..you rock!! It works perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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