Using VLOOKUP to reference Epic Name from another sheet

Hello SS Community,

I have a Jira to Smartsheet connector populating 2 sheets - one has 1 project's issues and another has all project's epics. I'm trying to populate the Epic Name on Issues on my 1st sheet so I can Group them in a Report. The formula in the 1st sheet (=IF([Issue Type]@row <> "Epic", VLOOKUP([Epic Link]@row, {Product Increment Data Epic}, 3)) returns an Epic name value, but it's for a different epic than the one the issue is linked to. Can you point out what I'm doing wrong? Below is the original sheet w/formula and below that is the VLOOKUP sheet I'm referencing. Thanks so much! -kerri


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kerri S

    It looks like you don't have a "match type" specified in your VLOOKUP function. The default is to find an approximate match, so it will find the first close match to what you're looking for.

    Try adding in a Match Type of "False" so it knows that you need an exact match, like so:

    =IF([Issue Type]@row <> "Epic", VLOOKUP([Epic Link]@row, {Product Increment Data Epic}, 3, false))

    See: https://help.smartsheet.com/function/vlookup

    Cheers!

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kerri S

    It looks like you don't have a "match type" specified in your VLOOKUP function. The default is to find an approximate match, so it will find the first close match to what you're looking for.

    Try adding in a Match Type of "False" so it knows that you need an exact match, like so:

    =IF([Issue Type]@row <> "Epic", VLOOKUP([Epic Link]@row, {Product Increment Data Epic}, 3, false))

    See: https://help.smartsheet.com/function/vlookup

    Cheers!

    Genevieve

  • Kerri S
    Kerri S ✭✭

    Got it, thanks. I wound up using Index Match and it returned correct results, but I'm glad to understand that setting in VLOOKUP. Appreciate the support.

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! 🙂

    If you're using INDEX(MATCH, you may come across the same issue - make sure the MATCH has a 0 at the end to identify that the list is unsorted:

    =INDEX({Column to Return}, MATCH([Value]@row, {Match Column}, 0))

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!