Archived 2017 Posts
Archived 2017 Posts
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.
Excel lookup formula in smartsheet
I have a risk register created in excel that has a tab named "lists". There are lookup formulas in the risk register that references data in the "lists" tab. Since smartsheet won't let me make tabs to my Register, how can I make the copied formula work.
Here is an example of the formula:
=IF($D4="","",AVERAGE(VLOOKUP($D4,Lists!$K$1:$S$6,9,0),(VLOOKUP($E4,Lists!$L$1:$S$6,8,0))))
I have attached .png files of the register, and the lists
Comments
-
The simplest way is to put your LISTS data into the top or bottom of your sheet. Use hierarchies to collapse it so it doesn't get in your way visually.
Use the LOOKUP function in a similar manner to your VLOOKUP usage.
I hope this helps.
Craig -
I am very new to SmartSheet. Could you please format the formula showing how it should look in SmartSheet using the into in the lists.png ( the last image file )
Many, many thanks if you can help with this.
Ralph
-
Ralph,
Try here:
https://help.smartsheet.com/topics/formulas-and-functions
https://help.smartsheet.com/function/lookup
That should get you started.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.3K Get Help
- 462 Global Discussions
- 156 Industry Talk
- 508 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 517 Show & Tell
- 35 Member Spotlight
- 3 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Trending Posts
- I have been trying to genrate a formula to shorten company names: 1- For multi word names, extract the first letter of each word, skipping over short words like "and", "the", etc. 2- For single word n…
- I am trying to calculate days open between the request date and today and not count weekends however I want the count to start at 0, not 1. I am using the below formula but for everything with today a…
- Good afternoon, I am used to INDEX/MATCH but this time I need to match two columns and then pull the information in a third column. Here is where I currently am: =INDEX(COLLECT({Meeting Planner}, {Mee…