英文:
Google Sheets vlookup to prioritize a column
问题
不确定是否有人能帮忙。
在附上的第2个选项卡上，我正在尝试进行vlookup到第1个选项卡，使用第1列的“code”来获取第3列的“email”。但是，如果第1列中有多个匹配的“code”，则优先选择第1个选项卡中列2中具有Level 1的行。例如，在选项卡1上有两个code 20，但是公式应该带回第5行 - test4@live.co.uk，而不是第2行 - test1@live.co.uk，因为第5行在列2中具有Level 1。如果列1中没有重复的code，只需将列1中的第一个结果带回。
在选项卡2的列3中已经有一个公式，但它不起作用，因为如果列2中没有Level 1，则不会带回任何内容，参见列4。在选项卡3上，我有一个正确结果的示例。这可以是vlookup、match＆index或任何有效的方式，只要在谷歌表中都可以。
除了上述内容，它需要基于列1中有某些内容并具有iferror的数组。谢谢。
英文:
Wonder if anyone can help.
On the attached on tab 2 I'm trying to do a vlookup to tab 1 to use column 1 'code' to bring back column 3 'email'. But if there is more than 1 match in column 1 for 'code', prioritize the row that has Level 1 in column 2 in tab 1. For example, there are two code 20, on tab 1 but the formula should bring back row 5 - test4@live.co.uk rather than row 2 - test1@live.co.uk as row 5 has Level 1 in column 2. If there is no duplicate code in column 1 just bring back the first result in column 1.
I've got a formula already in column 3 in tab 2 but it isn't working as it doesn't bring back anything if there isn't level 1 in column 2, see column 4. On tab 3, I've got an example of what the right result should be. This can be either a vlookup, a match & index or what ever works as long as its in google sheets.
Addition to the above, it needs to be an array based on something being in column 1 and have an iferror, just in case. Thanks.
答案1
得分: 2
=INDEX(IFNA(VLOOKUP(A2:A, SORT(LookTable!A:C, 2,), 3,)))
上述代码优先考虑 Level 1 而不是空白级别。如果可能会有数十个级别，我必须选择 Level 15 而不是 Level 8 等等，那么可以使用以下备用变体：
=INDEX(IFNA(VLOOKUP(A2:A, SORT(LookTable!A2:C, --IFNA(REGEXEXTRACT(LookTable!B2:B,"\d+")),), 3,)))
英文:
You may try:
=index(ifna(vlookup(A2:A,sort(LookTable!A:C,2,),3,)))
the above code prioritizes Level 1 over blank level. Not sure if there's goin' to be 10s of levels and I have to choose say level 15 over level 8 or so; then use this alternate variant
=index(ifna(vlookup(A2:A,sort(LookTable!A2:C,--ifna(regexextract(LookTable!B2:B,"\d+")),),3,)))
答案2
得分: 2
=XLOOKUP() 也应该支持最后到最前的 search_mode。尝试-
=XLOOKUP(A2,LookTable!A:A,LookTable!C:C,"",-1)
对于动态溢出数组，使用-
=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),
LAMBDA(x,XLOOKUP(x,LookTable!A:A,LookTable!C:C,"",-1)))
英文:
XLOOKUP() should also work with search_mode last to first. Try-
=XLOOKUP(A2,LookTable!A:A,LookTable!C:C,"",,-1)
For dynamic spill array, use-
=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),
LAMBDA(x,XLOOKUP(x,LookTable!A:A,LookTable!C:C,"",,-1)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论