I need to lookup a column on one sheet and reference a column and value on another sheet.

Robin Hannah
Robin Hannah ✭✭
edited 09/05/20 in Smartsheet Basics

I have a Model# on sheet 1. I need to search for the same Model# on sheet 2 and if found, look at that Model's status and return that value to sheet 1. Caveat: the Model Status on sheet 2 is a drop down list (Complete, Not Complete), so I need whatever value is selected to pull into sheet 1.

I tried a VLOOKUP using the formula below. It returned a NO MATCH even though the value is there. I also tried an INDEX with a VLOOKUP and no luck.

=VLOOKUP([Model #]2, {Check List Range 2}, 2, false)

Tags:

Best Answers

  • Robin Hannah
    Robin Hannah ✭✭
    edited 09/08/20 Answer ✓

    @Paul Newcome THANK YOU! It worked. I appreciate you! One more question. I know, I know. Is there a way to change the message that returns? Instead of NO MATCH I would like to say Not Complete or should I just do conditional formatting? I'm asking because these statuses will be viewed by executives and I don't want to keep explaining what no match means.

  • Robin Hannah
    Robin Hannah ✭✭
    Answer ✓

    @David Joyeuse Unfortunately, your formual returned all results as Not Complete even the ones that displayed a status of "Complete" previously. I hate to be a pain.

    So I entered this formula instead and it worked. Thank You!

    =IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Developer Check List Range 2}, 0)), "Not Complete")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You will want to wrap your formula in an IFERROR.

    =IFERROR(original_formula, "Not Complete")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Robin Hannah

    =INDEX({Model Status Range Sheet 2}, MATCH([Model# - Sheet 1]@row, {Model# Range Sheet 2},0))

    That should be working.

    Hope it helped!

  • Robin Hannah
    Robin Hannah ✭✭
    edited 09/07/20

    @David Joyeuse Thank you so much for responding. It should be working as it found one match from Sheet 2 but there are three matches, so two of the matches are not recognized. Any thoughts?

    My formula using the real sheet names:

    =INDEX({Dev Check List Range 1}, MATCH([Offline Model #]@row, {Dev Check List Range 2}, 0))

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    @Robin Hannah

    If the MATCH found a result, then I'd say the problem does not lie in the formula. Maybe there's a difference between what you think is a match? Like a number on one cell that is seen as a string on the other. Or some Caps here and there that the formula doesn't consider as matches?

    Could you provide us a screenshot of both sheets? with no sensitive datas or confidential ones on them?

  • Robin Hannah
    Robin Hannah ✭✭
    edited 09/08/20

    @David Joyeuse Here are screen shots below. I'm not sure if the sort order matters or not but I was unable to sort the Off column in ASC order even though the data element is text/number. I even copied the column and performed a sort (since the original is a primary) and it did not sort.

    Also is there a way to state "Not Complete" instead of No Match?

    Sheet 1


    Sheet 2


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly is the data being entered into the Off and Offline columns?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Robin Hannah
    Robin Hannah ✭✭
    edited 09/08/20

    @Paul Newcome For sheet 1 the information is static. I imported the data in and it will not be refreshed. For Sheet 2 The data is the result of a formula in the column adjacent to it that has a long description. The formula copies the first 4 letters or the offline number itself without the text description.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I think that may be the problem. Some are text values whereas some are numerical values. Try inserting a helper column into each sheet and use

    =Off@row + ""

    and

    Offline@row + ""

    respectively.

    This converts everything into text values. Comparing these two helper columns means you are now comparing like data types.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Hi I inserted helper columns as you recommended, now the formula is not yielding any results although there are matches. I don't know what else to do.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you using the helper columns in your formula? Can you post a screenshot of the data now along with copy/pasting the exact formulas from the sheets?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sheet 1

    Offline Model column formula =Offline@row + ""

    Model Status-1B column formula =INDEX({Check List Range 2}, MATCH([Offline Model]@row, {Check List Range 1}, 0))

    Check List Range 2 is the Model column in Sheet 2

    Check List Range 1 is the Status column in Sheet 2


    Sheet 2

    Model_No column formula =LEFT([Model#]1, 4)

    Model column formula =[Model_No]@row + ""



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. The problem is with your ranges. You have them flipped around. Think of it this way... You want to INDEX (or pull) from the Status column based on a MATCH in the Model column.

    As is, you are INDEXing (or pulling) from the Model column based on a MATCH in the Status column.


    Try switching your ranges around and see if that clears it up for you.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Robin Hannah
    Robin Hannah ✭✭
    edited 09/08/20 Answer ✓

    @Paul Newcome THANK YOU! It worked. I appreciate you! One more question. I know, I know. Is there a way to change the message that returns? Instead of NO MATCH I would like to say Not Complete or should I just do conditional formatting? I'm asking because these statuses will be viewed by executives and I don't want to keep explaining what no match means.

  • Robin Hannah
    Robin Hannah ✭✭
    Answer ✓

    @David Joyeuse Unfortunately, your formual returned all results as Not Complete even the ones that displayed a status of "Complete" previously. I hate to be a pain.

    So I entered this formula instead and it worked. Thank You!

    =IFERROR(INDEX({Check List Range 1}, MATCH([Offline Model]@row, {Developer Check List Range 2}, 0)), "Not Complete")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You will want to wrap your formula in an IFERROR.

    =IFERROR(original_formula, "Not Complete")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com