如何返回一个以上的值与多个搜索值与vlookup

安德烈·拉莫斯
安德烈·拉莫斯 ✭
在公式与函数

你好,

我有两个列ID和C.基于一个vlooup公式在C与ID的搜索值，需要显示在C的值。

我的问题是，当我需要显示基于多个id的多个值。例如:如果ID1=Result1 ID2=Result2和ID3=Result3，在ID列中我写1 2或类似的东西，我想在C列中显示Result1和Result2。

注意:ID和C值基于另一个Sheet。

谢谢你！

Smartsheet.png


标签:
· 在Facebook上分享 在推特上分享

答案

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板!
@Pamela Wagner<\/a> <\/p>

Here's an example sheet and formula I made for someone else's topic once.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

=COUNTIFS([Teams Responsible for Testing]:[Teams Responsible for Testing], CONTAINS(\"MIS\", @cell), Status:Status, <>\"Complete\")<\/p>

This is used to find \"MIS\" in the Teams Responsible for Testing column if the Status column is not \"Complete\". It's finding 4 results.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-23 00:00:06","updateUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-22T23:55:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":99015,"type":"question","name":"Nested Criteria on COLLECT","excerpt":"I'm trying to get an income by multiplying quantity with Price. Price is located on another sheet so I have to find it based on type, route, and active date. Here's the formula that I have: =IFERROR([Economy QTY]@row * (INDEX(COLLECT({Price Range}; {Type Range}; \"Economy\"; {Route Range}; CONTAINS(Route@row; @cell); {Active…","categoryID":322,"dateInserted":"2022-12-22T09:37:59+00:00","dateUpdated":"2022-12-22T10:15:13+00:00","dateLastComment":"2022-12-23T04:37:44+00:00","insertUserID":156222,"insertUser":{"userID":156222,"name":"Ardneran","url":"https:\/\/community.smartsheet.com\/profile\/Ardneran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-23T09:42:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":156222,"lastUserID":156222,"lastUser":{"userID":156222,"name":"Ardneran","url":"https:\/\/community.smartsheet.com\/profile\/Ardneran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-23T09:42:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":44,"score":null,"hot":3343475143,"url":"https:\/\/community.smartsheet.com\/discussion\/99015\/nested-criteria-on-collect","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99015\/nested-criteria-on-collect","format":"Rich","tagIDs":[254,472],"lastPost":{"discussionID":99015,"commentID":355280,"name":"Re: Nested Criteria on COLLECT","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355280#Comment_355280","dateInserted":"2022-12-23T04:37:44+00:00","insertUserID":156222,"insertUser":{"userID":156222,"name":"Ardneran","url":"https:\/\/community.smartsheet.com\/profile\/Ardneran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-23T09:42:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-23T04:37:50+00:00","dateAnswered":"2022-12-23T04:37:44+00:00","acceptedAnswers":[{"commentID":355280,"body":"

Update :<\/p>

I think I figured it out, the formula has two @cells so I think there might be some inconsistency. I changed how my sheet works by modifying some columns so I don't have to use @cell. Thank you all.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-23 04:37:50","updateUser":{"userID":156222,"name":"Ardneran","url":"https:\/\/community.smartsheet.com\/profile\/Ardneran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-23T09:42:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":99021,"type":"question","name":"How to add a prefix to a project ID","excerpt":"Hi Community, I am trying to create a project ID column that generates a prefix based on the project type. I'd like the numbers to go in sequential order for each project. Ex: abc001, abc002, def001, abc003, def002 Additionally, I would like for it to only generate a number if another column specifies \"New Project.\" Any…","categoryID":322,"dateInserted":"2022-12-22T15:31:38+00:00","dateUpdated":null,"dateLastComment":"2022-12-22T21:31:26+00:00","insertUserID":151306,"insertUser":{"userID":151306,"name":"Ddabbs","url":"https:\/\/community.smartsheet.com\/profile\/Ddabbs","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-22T20:25:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2022-12-22T21:35:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":63,"score":null,"hot":3343473184,"url":"https:\/\/community.smartsheet.com\/discussion\/99021\/how-to-add-a-prefix-to-a-project-id","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99021\/how-to-add-a-prefix-to-a-project-id","format":"Rich","lastPost":{"discussionID":99021,"commentID":355264,"name":"Re: How to add a prefix to a project ID","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355264#Comment_355264","dateInserted":"2022-12-22T21:31:26+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2022-12-22T21:35:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-22T18:05:19+00:00","dateAnswered":"2022-12-22T17:57:37+00:00","acceptedAnswers":[{"commentID":355222,"body":"

@Ddabbs<\/a> <\/p>

Create an Auto Number column with your preferred number format. Then create a column which builds your Project ID based on another column which helps determine the abc or def prefix and then adds the Auto Number.<\/p>

You'll need to create a formula which is something like:<\/p>

=IF([Project Type]@row=\"Alpha\", \"abc\"+[Auto Number]@row, IF([Project Type]@row=\"Beta\", \"def\"+[Auto Number]@row))<\/p>"},{"commentID":355223,"body":"

@Ddabbs<\/a> I think this question gets asked 3-4 times year.<\/p>

There are a few ways to do this, but they all start with having separate intake sheets for each project type, and moving those rows to a main project list sheet. This is because you need independent Auto-number columns for each project type, and the only way to do that in core Smartsheet is with separate sheets.<\/p>

Alternatively, you could have one intake sheet without an Auto-number column, which then moves rows to separate project type sheets based on project type where each project sheet contains a prefixed Auto-number column, and have automation move those rows to a main project sheet. That flow would look like this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-22 18:05:10","updateUser":{"userID":151306,"name":"Ddabbs","url":"https:\/\/community.smartsheet.com\/profile\/Ddabbs","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-22T20:25:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">