Hlookup Help

Options

Hey, I have a formula currently in a spreadsheet that is an Hlookup, and I am super stuck recreating it in SmartSheet. I have tried the index match, but it is still not working. I have it attached but basically, I have 2 factors "likelihood" and "impact" that drive the "severity". There is a table that says what the "severity" is based on what you pick for the "likelihood" and "impact". The table is on a different tab. The current excel formula is in Column C.

I want to recreate the exact same thing in Smartsheet using one sheet as the "risk decision table" and then have a second sheet called- "master sheet" with the "likelihood" and "impact" to drive the "severity" column formula on my master sheet.



Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @lreynolds

    Overcame something similar by combining the two criteria into one so a simple index match will do the trick.

    If you add a hidden helper column into your sheet i.e LOOKUP VALUE

    You can structure however you want but something like this works for me: =Likelihood@row + " / " + Impact@row

    You don't require a helper column in the front sheet as you can write this into the index match formula directly, this example formula works if everything is in the same sheet but you can amend for cross sheet purposes.

    =INDEX(Severity:Severity, MATCH(Likelihood@row + " / " + Impact@row, [Lookup value]:[Lookup value], 0))

    Its a workaround but has worked well for me.

    Hope it helps

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @lreynolds

    Overcame something similar by combining the two criteria into one so a simple index match will do the trick.

    If you add a hidden helper column into your sheet i.e LOOKUP VALUE

    You can structure however you want but something like this works for me: =Likelihood@row + " / " + Impact@row

    You don't require a helper column in the front sheet as you can write this into the index match formula directly, this example formula works if everything is in the same sheet but you can amend for cross sheet purposes.

    =INDEX(Severity:Severity, MATCH(Likelihood@row + " / " + Impact@row, [Lookup value]:[Lookup value], 0))

    Its a workaround but has worked well for me.

    Hope it helps

    Thanks

    Paul

  • lreynolds
    Options

    This worked GREAT!!! Thank you so much for your help!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!