英文:
Issue writing vlookup for filling values based on filter for data over a table
问题
无法创建正确的VLOOKUP函数来填充附加的图像中的B8、B9、B10，这些数值应该来自表格A12:G15，根据B1中选择的筛选值。您能提供一个正确的VLOOKUP函数来解决这个问题吗？
英文:
I am unable to create a proper VLOOKUP to fill B8, B9, B10 in the image attached. which are derived from the table A12:G15, based on filter value selected in B1. could you help with a proper VLOOKUP command to solve this issue.
I tried to concatenate and add multiple cells like suresh+july, Suresh+august but this inturn increases length of table which needs to be avoided for simplicity.
答案1
得分: 1
尝试使用 INDEX() 和 MATCH()。
• 单元格 B8 中使用的公式：
=INDEX($B$13:$G$15,MATCH(A8,$A$13:$A$15,0),MATCH($B$1,$B$12:$G$12,0))
或者，单元格 B8 中使用的公式：
=SUMPRODUCT(--(A8=$A$13:$A$15)*($B$1=$B$12:$G$12)*$B$13:$G$15)
或者，使用 XLOOKUP() 和 FILTER() 函数。
=XLOOKUP($A8,$A$13:$A$15,FILTER($B$13:$G$15,$B$1=$B$12:$G$12))
或者，如果您想要使用 VLOOKUP()，那么
• 单元格 B8 中使用的公式：
=VLOOKUP($A8,$A$13:$G$15,MATCH($B$1,$A$12:$G$12,0),0)
英文:
Try using <kbd>INDEX( )</kbd> & <kbd>MATCH( )</kbd>
• Formula used in cell B8
=INDEX($B$13:$G$15,MATCH(A8,$A$13:$A$15,0),MATCH($B$1,$B$12:$G$12,0))
Or, Formula used in cell B8
=SUMPRODUCT(--(A8=$A$13:$A$15)*($B$1=$B$12:$G$12)*$B$13:$G$15)
Or, Use <kbd>XLOOKUP( )</kbd> with <kbd>FILTER( )</kbd> function
=XLOOKUP($A8,$A$13:$A$15,FILTER($B$13:$G$15,$B$1=$B$12:$G$12))
Or, if you want to use <kbd>VLOOKUP( )</kbd> then
• Formula used in cell B8
=VLOOKUP($A8,$A$13:$G$15,MATCH($B$1,$A$12:$G$12,0),0)
答案2
得分: 0
也可以使用 FILTER：
=FILTER(FILTER($B$13:$G$15,($B$12:$G$12=$B$1)),$A$13:$A$15=A8)
结果：
英文:
Also possible with FILTER:
=FILTER(FILTER($B$13:$G$15,($B$12:$G$12=$B$1)),$A$13:$A$15=A8)
Result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论