Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts

    Using Vlookup to look up a value

    I have used the following formula to look up the account number in col B and to give me the value of the account number which appears in workbook BR1TB. It gives me a value #N/A.

    =VLOOKUP(B3,BR1TB.xls!$A:$E,5,1)

    For eg Acc # 102535 should give me a value of -3470.42

    Attached please find sample data

    It would be appreciated if someone could assist
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,005
    Thanks
    423
    Thanked 1,608 Times in 1,452 Posts
    Howard,

    The reason the VLookup doesn't work is the formula you have in Col B. of the NV Sales sheet in the Account Numbers.xls file generates a TEXT value {i.e. they are preceded by an apostrophe ('102535) and the account numbers in the BR1TB.xls file are numbers. If you remove the apostrophe it works just fine as shown below.

    Another way to fix the problem is to change your VLookup function as follows:
    =VLOOKUP(VALUE(B3),BR1TB.xls!$A:$E,5,TRUE)
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts
    Thanks for the help. I will use your formula which I like

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,354
    Thanks
    4
    Thanked 229 Times in 210 Posts
    FYI, unless you can guarantee that the account numbers appear in both lists and that the source table is sorted in ascending order, I would recommend you use:
    =VLOOKUP(VALUE(B3),BR1TB.xls!$A:$E,5,FALSE)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,005
    Thanks
    423
    Thanked 1,608 Times in 1,452 Posts
    Rory,

    Thanks, you're exactly right. I was a little sleepy when I wrote that.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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