Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
LOOKUP & MATCH together
Hi everyone, I use a VLOOKUP and MATCH function in Excel to find the intersection of vertical and horizontal values (intersection of a matrix / table). I can't seem to get this working in Smartsheet and need some help.
For example if I have Likelyhood = Likely and Impact = Moderate then I want the Criticality to return the value "Significant".
Comments
-
This is where CSE or array formulas would be nice in SmartSheet.
-
Hi Brendon,
Since your lookup table exists on the sheet, you can use our LOOKUP function with our MATCH function to accomplish this.
The first arguement of the LOOKUP would be the Likelihood cell reference as this is the first columns in your table. Then you would use the MATCH function to determine which column the Impact cell refernce is found in and pass it to the LOOKUP function as a column number. As the values aren't in alpha-numeric order, you will need to add a false in the last argument.
=LOOKUP([Gross Risk: Likelihood]1, [Column30]$1:[Column20]$6, MATCH([Gross Risk: Impact]1, [Column30]$1:[Column20]$1, 0), false)
Let me know if you have any questuons on this.
Taylor
-
Awesome Taylor, ka pai.
I must've had something out of place as I'd tried that. Worked a treat... another SmartSheet replacing Excel.
