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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives