Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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,420
    Thanks
    124
    Thanked 5 Times in 5 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,280
    Thanks
    3
    Thanked 191 Times in 177 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
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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
  •