Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Cannot get Vlookup to Work

    I have a file called "stock Report Sample"


    I have a Vlookup formula in Col and am trying to make the Chassis No. in Col A with the Vin No. in Col F "Supplier listing sample"


    If the two numbers match, then formula must return "Item in Stock" , "item not on stock report"


    It would be appreciated if someone could assist
    Attached Files Attached Files
    Last edited by HowardC; 2016-02-24 at 12:58.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Howard,

    Two points about VLOOKUP:

    1. The values you are searching through (the second argument in the VLOOKUP function) must be in the first (left hand-most) column of the specified search range.

    2. VLOOKUP doesn't return TRUE or FALSE in the way you want. It looks up a value in the same row as it found a match, in the column you specify. If the item you are searching for isn't present, VLOOKUP will return #N/A.

    You could process the result of the VLOOKUP into "In stock" or "Not in stock" with an IF(ISERROR(....)) test, but why don't you simply use COUNTIF ? Much simpler, faster and uses fewer Excel resources.

    There's a description of the COUNTIF function here http://www.techonthenet.com/excel/formulas/countif.php
    Last edited by MartinM; 2016-02-24 at 16:33.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    HowardC (2016-02-24)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Martin

    Thanks for your reply & Input. I have used the countif formula as follows and it gives me the desired result

    Code:
     =IF(COUNTIF('[Supplier Listing Sample.xls]Sheet1'!$F:$F,A2)=0,"not in Stock","in stock")

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Good news.

Posting Permissions

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