How do I write a formula similar to a lookup in Excel?

I would like to populate a field in the Summary of a sheet with a contact name from another sheet. I am looking for help to write a formula that will say:

Find the row where the words 'Project 25' is in a Column titled Project Name, in Sheet 'All Projects' and return the text in the column of the same row titled "Manager"

I have been given: =IFERROR(INDEX(COLLECT({Program Owner}, {Opportunity Number}, [Parent Opportunity Number]#), 1), "<<PROGRAM OWNER>>") which works to bring back the Program Owner but when I replace the word Program Owner with Manager, it doesn't work.

Any help would be greatly appreciated.

Thankyou

Find more posts tagged with

Formulas

Accepted answers

Mike Wilday

Awesome. Glad we could help you out. Can you please accept our answers?

All comments

Mark Cronk

Hi @Trinity Brookes ,

Try a simply vlookup:

=vlookup("Project 25", {project name}, 3) where {project name} is the data range with the name column on the left going right to the manager name column, and change 3 to be the number of columns between the project name and manager name. As you enter the formula the option will come up to reference another sheet. Select that and find the data range you want.

Confirm that the summary field property is correct for the data going in. Default is text but you can change it to contact list if you're pulling from a contact list field.

Work?

Mark

Mike Wilday

Here is more information on the Vlookup Formula.

https://help.smartsheet.com/function/vlookup

Did this help?

Trinity Brookes

Thankyou, I've managed to solve this issue.

Mike Wilday

Awesome. Glad we could help you out. Can you please accept our answers?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.