如何使用纸张中的索引＆＃x2B; vlookup动态获取标准列？

发布于 2025-01-23 04:42:14 字数 395 浏览 0 评论 0原文

一个好灵魂帮助我达到了这一点，但是我要解决的又一个违反了一个：

如何基于填充 rep ID 列，但是此列可能会移动，因此获得此功能必须是动态的，就像获得 Rep 和 region 列一样。

这是工作公式，没有标准：

=INDEX(VLOOKUP(ROW('From Sheet1'!A7:A); {ROW('From Sheet1'!A7:A)\ 'From Sheet1'!B7:C}; MATCH({"Rep"\ "Region"}; 'From Sheet1'!A7:C7; 0); 0))

像往常一样感谢您的帮助！

原文

One good soul has helped me get to this point, but I got one more contraint I'm trying to tackle:

How to populate Rep and Region based on the ID column, but this column may move, so getting this must be dynamic, just like getting Rep and Region columns is.

Here's the working formula, without the criteria:

=INDEX(VLOOKUP(ROW('From Sheet1'!A7:A); {ROW('From Sheet1'!A7:A)\ 'From Sheet1'!B7:C}; MATCH({"Rep"\ "Region"}; 'From Sheet1'!A7:C7; 0); 0))

Here's a sample data.

Appreciate your help, as usual!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最丧也最甜 2025-01-30 04:42:14

尝试：

=INDEX(IFNA(VLOOKUP(A7:A, 
 {FILTER('From Sheet1'!A7:Z, 'From Sheet1'!7:7="ID"), 'From Sheet1'!B7:C}, 
 MATCH({"Rep", "Region"}, 'From Sheet1'!A7:C7), 0)))

try:

=INDEX(IFNA(VLOOKUP(A7:A, 
 {FILTER('From Sheet1'!A7:Z, 'From Sheet1'!7:7="ID"), 'From Sheet1'!B7:C}, 
 MATCH({"Rep", "Region"}, 'From Sheet1'!A7:C7), 0)))

回复收藏 0 原文

原来是傀儡 2025-01-30 04:42:14

这是

=ARRAYFORMULA(VLOOKUP(A7:A,{INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4)))},{2,3}))

您在Cell B7中拥有的该公式的愚蠢的，这也假设您的标题在Enger1上的第7行中。

This one is a doozy

=ARRAYFORMULA(VLOOKUP(A7:A,{INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4)))},{2,3}))

In the place of that formula you had in cell B7, This is also assuming your headers are in row 7 on sheet1.

回复收藏 0 原文

~没有更多了~