LOOKUP & MATCH together

Brendon McHugh

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".

 

Matt Hines

This is where CSE or array formulas would be nice in SmartSheet. 

Taylor F

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

Brendon McHugh

Awesome Taylor, ka pai.

 

I must've had something out of place as I'd tried that. Worked a treat... another SmartSheet replacing Excel.