Vlookup functions not working on a Master Spreadsheet

My colleague and I, who do extensive work on a master spreadsheet that pulls data from many different sheets, are having a lot of trouble with the vlookup function on this particular sheet. We use the vlookup function in multiple different columns on the spreadsheet in question, pulling data from a different source sheet in almost every case. In addition to that, some the data that we pull from the different source sheets is in fact obtained by a vlookup function. So the chain would be raw data -> vlookup to middle sheet -> vlookup to master sheet. 

We have been finding that many of the vlookup functions on sheet in question simply do not work. In all cases, the function syntax is correct. In some cases, the result shows "No Match", which would not make sense because we have triple and quadruple checked the search values on connected sheets to ensure accuracy. In other cases, the first row will pull an accurate data point from the source sheet, but then when we drag the function down, the cells below either come back with a "no match" message or a random number or two are copied over and over again. This is indicated in the right column in the image. 


Any ideas as to why this would be happening? 

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @SPO MPD

    I hope you're well and safe!

    Can you share the exact formula you're using?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SPO MPD
    SPO MPD ✭✭
    edited 02/26/21

    The spreadsheet compiles a significant amount of private information on our staff, so I can't share the spreadsheet.

    The formula I'm using is:

    =VLOOKUP([Full Name]@row, {Roll Forward Balances Range 1}, 23)

    Roll Forward Balances is the sheet the vlookup pulls from. Column 23 is the column on the source sheet. Said column is also a vlookup function, which pulls data from another sheet. As I mentioned in the original post, the Full Name column, which is the search value in the function, has been matched to the Full Name column on the source sheet.

    Hopefully this is helpful.