+ Reply to Thread
Results 1 to 11 of 11

Vlookup is not functioning

  1. 11-23-2010, 06:54 AM #1
    greekboyuk
    greekboyuk is offline
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    Vlookup is not functioning

    Hello,

    I have a problem with a vlookup formula.

    I have a value in cell AM2 wich has this formula : 
    Please Login or Register  to view this content.
    I have a table called ManagersData in my Database sheet.

    In cell C3 I am using the follwing formula:
    Please Login or Register  to view this content.
    and copying this down.

    It should bring me all the values that are looked up for cell AM2 however it is only displaying 5 values instead of 6 and I don't understand why. All the cells formating are the same and I've tried to create a new spreadsheet and the problem still persist.

    Any ideas please??
    Reply With QuoteRegister To Reply
  2. 11-23-2010, 06:58 AM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Vlookup is not functioning

    please post a workbook,no one wants to try and reconstruct one from your limited info. doing so will probably get you the help you need.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 11-23-2010, 06:59 AM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup is not functioning

    Hi,

    Upload your workbook (or just the relevant part of it) so that we can see your problem in context. It's very difficult to identify the problem without seeing the actual data.

    Rgds
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 11-23-2010, 08:50 AM #4
    greekboyuk
    greekboyuk is offline
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    Re: Vlookup is not functioning

    hi,

    Thank you for trying to help.

    I have attached a sample of my spreadsheet.

    Many Thanks

    greekboyuk
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-23-2010, 09:19 AM #5
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Vlookup is not functioning

    if you change row(2:2) to row(1:1)
    youll get this result

    Nick Smith
    John Pine
    Elaine Loris
    Mark Saxon
    Lisa Martin
    Tracey Niffis
    Mike Doger
    Lucy Santos
    vlookup is one column out change to
    =IF(ISERROR(VLOOKUP($C2,ExtAccess,COLUMN(C1),0)),"",VLOOKUP($C2,ExtAccess,COLUMN(C1),0)) in cell E2
    zero is returned as the vlook up is not an error and blank cells show as 0 in a formula(try =B1 when b1 is blank you'll get zero)
    format cells as custom
    0;-0;;@
    to hide zeros
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 11-23-2010, 10:02 AM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup is not functioning

    Hi,

    Why not just avoid using the Mail Managers sheet altogether and introduce a new column (say column C) for the manager on the Ext Link sheet, then C2 copied down becomes
    Please Login or Register  to view this content.
    HTH
    Reply With QuoteRegister To Reply
  7. 11-23-2010, 10:22 AM #7
    greekboyuk
    greekboyuk is offline
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    Re: Vlookup is not functioning

    Hello martindwilson,

    Thank you very much for you help.The first part is now sorted thanks to you. I still dont get a result after Lucy Sandon. It is blank. On that It should search for Manager on A10 and bring the Employees from the Database on cell C10.

    Richard nice idea however it is not possible.
    The ExtLink is and external report that changes and it is set like that. So if I add any rows or columns and sm1 runs a report it will change the results.

    Thank you
    Reply With QuoteRegister To Reply
  8. 11-23-2010, 12:04 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Vlookup is not functioning

    thing is that array will only return multiples for one manager . how many managers are you likely to have?
    Reply With QuoteRegister To Reply
  9. 11-23-2010, 12:07 PM #9
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup is not functioning

    Hi,

    One further thought then. You could write a macro to copy the ExtLink sheet, insert column and then add the formula. The macro could either be run manually immediately after the data is refreshed or make it an automatic event driven macro. You'd obviously have to code the macro to first delete the previous ExtLink copy sheet.

    HTH
    Reply With QuoteRegister To Reply
  10. 11-23-2010, 12:51 PM #10
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Vlookup is not functioning

    look at sheet data! pick up to 3 managers to mail ,sheet 1 fills in from this
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 11-25-2010, 06:33 AM #11
    greekboyuk
    greekboyuk is offline
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    Re: Vlookup is not functioning

    Hello Richard & Martin,

    Thank you for the support and help. We have more that 200managers so the 3 mangers solution it cannot be used.
    I have a similar solution that Martin attached on the latest one which you could email all the managers. Basically links all the database sheet Column A and B, to the MailManager sheet A and C. The problem with this solution that I found while testing, if you cut or move data into the database sheet, the MailManager sheet instead of updating it automatically it moves the cell link or displays REF#.If I could avoid this problem I could use this medothod instead.

    Sample attached

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1