Two Vlookup Formulas in one cell to retrieve data from two sheets

Options

Hi everyone. So my journey in taking over a project continues, and lets just say the sheets that were put together is a big challenge. I have two sheets, Sheet 1 is titled "CY23 eAPP Station - Background Investigati ) and Sheet 2 is titled CY23 eAPP Station (C) - Background Investigati". On the third sheet there is a cell that has to look for information in Sheet 1 and Sheet 2 and place the date value from either sheet 1 or sheet 2 in a column thats in Sheet 3.

I am using two Vlookups in one cell to do this, but it is not working. When I used just one Vlookup it returns a value but when trying to combine both VLookups into the cell the data from Sheet 2 is not populating in sheet 3. I am currently using the below formula:

=IFERROR(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false))

and have tried this formula as well

=JOIN(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false))

I have been working on this problem for about 3 hours and really need to get it fixed quickly. Can someone please help me. Thanks Brian

Answers

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

    Hi @Peppey

    I hope you're well and safe!

    It can get tricky.

    Will it always just be one of the sheets that will have information?

    Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.

  • Peppey
    Peppey ✭✭
    Options

    Good morning sir. Yes sir I will work on doing some screen shots (it will take a bit) and will than post. Thanks!

  • Peppey
    Peppey ✭✭
    Options

    Hi sir, Thank you again for your assistance. I have pasted the screen shots below and I hope this helps.

    This screen shot is from the roll-up sheet where I am placing the VLookup formula:

    The below is from the sheet titled "CY23 eAPP Station - Background Investigati" 

    Finally the one below is the exact same screen shot from sheet titled "CY23 eAPP Station (C) - Background Investigati" because both sheets are the same except different date ranges

    Thank you again for all of your assistance - I appreciate it.

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

    @Peppey

    Excellent!

    Happy to help!

    Try something like this.

    =IFERROR(IF(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false) = "", VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false)), "")

    Did that work/help?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!