Vlookup限制吗?
嗨，我想了解这个限制在vlookup:
- 每个表可以包含不超过100个不同的交叉表引用。
- 总共有25,000个入站单元格可以从其他工作表引用到一个工作表中。
你说的100个不同的交叉表参考是什么意思?
-如果引用相同的表，相同的标准，它是否被视为一个不同的交叉表引用?
25,000个入站细胞的限制?
-目的表只能“接收”25000个入站数据?
-如果我们有一个主表作为数据库，需要出站到许多表，出站可以超过25,000个单元格吗?我们的母表包含15列4000行。
我们已经开始建立一个数据库，其中信息可以拉出到其他表格以标准化数据输入，当我们意识到这个限制时，我们处于解决方案构建的末尾。
感谢建议和帮助更好地理解这一点。
评论
-
保罗新来的 ✭✭✭✭✭✭不同的交叉表引用是每个单独命名的范围。
{Sheet A Range 1}可以使用100次，但只算作1个不同的Range。
{表格A范围2}现在会给你两个不同的范围，以此类推。
如果您的范围包含多个列和/或行，它仍然只算作一个不同的范围。
．
您可以根据需要将输出链接到母表中的其他工作表。极限是传入的，这个极限是25000个细胞。
．
如果区域只有一列，长100行，则为1个不同的交叉表引用，并计为100个入站单元格。但是，它可以按照您的意愿多次使用，因此即使使用了500次，它仍然只会被计算为1个不同的范围和100个入站单元格。
thinkspi.com
-
薇薇安庄 ✭✭✭✭✭✭
谢谢保罗。你的解释简单明了。
非常感谢!
-
保罗新来的 ✭✭✭✭✭✭
-
不明白，范围1和范围2的区别是什么?
-
吉纳维芙P。 员工管理这是从另一个工作表引用的两个不同的列或范围。下面是一个可能使用两个范围的示例公式:
=INDEX({要从另一个表返回值的列}，MATCH([要匹配的值]@row，{另一个表中要匹配值的列}))
在这个例子中，有两个不同的范围:
- {包含要从另一个工作表返回的值的列}
- {要在另一个工作表中匹配值的列}
下面是第一个范围{Value to Return}的图片，可能是这样的:
然后，这将是第二个不同且不同的范围，具有不同的名称{Value to Match}:
由于这两个范围引用不同的列，因此它们是两个具有各自特定名称的不同引用。这样能解释清楚吗?如果我还需要进一步解释的话，请告诉我。
欢呼,
吉纳维芙
帮助文章参考资料欧宝体育app官方888
类别
=COUNTIFS([column4]2:[column4]5,\"Qualification\",[column5]2:[column5]5,\"Q4-2023\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":106590,"type":"question","name":"Helper checkbox not working on reference sheet","excerpt":"I have multiple line items for a order in one sheet(Request Sheet) and in another sheet (Order sheet) where I have clubbed all the items in single row, based on the order ID from request sheet to order sheet I want to check the Quantity checkbox and update in the Order sheet, I tried index match formula but its not working…","categoryID":322,"dateInserted":"2023-06-19T11:07:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T16:56:49+00:00","insertUserID":153826,"insertUser":{"userID":153826,"name":"Domnic Victor","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Domnic%20Victor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T22:11:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3374368455,"url":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106590,"commentID":381185,"name":"Re: Helper checkbox not working on reference sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381185#Comment_381185","dateInserted":"2023-06-19T16:56:49+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T22:11: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/TUXJPHZT569J\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T16:01:16+00:00","dateAnswered":"2023-06-19T13:27:24+00:00","acceptedAnswers":[{"commentID":381141,"body":"
The issue is it doesn't know whether to check the box or not check the box because there are multiple ORD0241 on the Request sheet and one is checked and one is not checked. If I am understanding correctly you want the box checked if any of the ORD0241 are checked on the request sheet. If that is correct you would need to use a countifs formula with an If formula<\/p>
=IF(COUNTIFS({Order ID reference from request sheet},[Order ID]@row,{Quantity Helper reference from request sheet},1)>=1,1,0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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"}]},{"discussionID":106591,"type":"question","name":"Want to flag overlapping dates AND times","excerpt":"Based on information I've found here, I've created a formula that identifies overlapping times that occur on the same day, but I'd like to adapt it to incorporate items that occur over multiple days. This will be used for booking of equipment. Here is what I have so far: =IF(COUNTIFS(SAP:SAP, SAP@row, [Start Date]:[Start…","categoryID":322,"dateInserted":"2023-06-19T12:43:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T15:01:21+00:00","insertUserID":162503,"insertUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162503,"lastUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3374367883,"url":"https:\/\/community.smartsheet.com\/discussion\/106591\/want-to-flag-overlapping-dates-and-times","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106591\/want-to-flag-overlapping-dates-and-times","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106591,"commentID":381158,"name":"Re: Want to flag overlapping dates AND times","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381158#Comment_381158","dateInserted":"2023-06-19T15:01:21+00:00","insertUserID":162503,"insertUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8XNE9V76RH1R\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T15:03:04+00:00","dateAnswered":"2023-06-19T14:56:38+00:00","acceptedAnswers":[{"commentID":381157,"body":"
Ok I think I got it. It's due to the times you need to combine the dates and times to get it to consider both as a whole and not individually. So you will need a helper column for your start date\/time and end date time. I titled mine as Start Date Time Helper and End Date Time Helper. If you title them the same you should be able to copy and paste formulas.<\/p>
Start Date Time Helper<\/p>
=IFERROR(VALUE(YEAR(DATEONLY([Start Date]@row)) + \"\" + IF(MONTH(DATEONLY([Start Date]@row)) < 10, \"0\") + MONTH(DATEONLY([Start Date]@row)) + IF(DAY(DATEONLY([Start Date]@row)) < 10, \"0\") + DAY(DATEONLY([Start Date]@row)) + IF([Start Time]@row < 1000, \"0\") + [Start Time]@row), \"\")<\/p>
End Date Time Helper <\/p>
=IFERROR(VALUE(YEAR(DATEONLY([End Date]@row)) + \"\" + IF(MONTH(DATEONLY([End Date]@row)) < 10, \"0\") + MONTH(DATEONLY([End Date]@row)) + IF(DAY(DATEONLY([End Date]@row)) < 10, \"0\") + DAY(DATEONLY([End Date]@row)) + IF([End Time]@row < 1000, \"0\") + [End Time]@row), \"\")<\/p>
Formula to get your Yes\/No's<\/p>
=IF(COUNTIFS(SAP:SAP, SAP@row, [End Date Time Helper]:[End Date Time Helper], @cell >= [Start Date Time Helper]@row, [Start Date Time Helper]:[Start Date Time Helper], @cell <= [End Date Time Helper]@row) > 1, \"No\", \"Yes\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
