Hlookup Help

lreynolds

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.

Hlook UP example Help.xlsx

Paul McGuinness

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

Paul McGuinness

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

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