英文:
How to find the row of a specified value than return a corresponding value on a different column Google Sheets
问题
我正在尝试在Google Sheets中编写一个公式，可以首先定位特定值所在的行，然后索引该行中相隔几列的值。
让我们假设以下情况
A B C
1 12 80
2 43 35
3 64 15
4 13 56
5 44 93
6 86 48
7 14 31
8 41 3
9 63 56
10 11 46
列B和列C中的值具有相关关系。我首先需要定位列B中特定值的行，然后在同一行中找到列C中对应的值。
举例来说，假设我要定位包含值41的行，然后想要返回列C中的相应值，这种情况下应该是3。
我需要这样的公式的原因是因为我使用的数据非常多变且庞大，超过4000行。不知道要查找的值位于哪些行。
英文:
I'm trying to write a formula in Google Sheets which can first locate the row of a specific value. Then index to the value contained on that row a few columns over.
Let's assume the following
A B C
1 12 80
2 43 35
3 64 15
4 13 56
5 44 93
6 86 48
7 14 31
8 41 3
9 63 56
10 11 46
Values in column B and C have a correlated relationship. I need to first locate a specific value in column B than find it's corresponding value on the same row in column C.
For the sake of example, let's assume I'm trying to locate the row containing the value 41 in column B. And then would like to return the corresponding value in column C, which in this case would be 3.
The reason why I need a formula like this is because the data I'm using is highly variable and large. Over 4000 rows. It is unknown what rows the values to be found sit on.
答案1
得分: 2
=filter(C:C,B:B=D2)
或者
=xlookup(D2,B:B,C:C,)
filter()将输出在列B中具有D2的所有行（列C），而xlookup将仅选择列中D2的第一个匹配项。
英文:
You may try either:
=filter(C:C,B:B=D2)
OR
=xlookup(D2,B:B,C:C,)
filter() will output all instances of rows(column C) which has 41 in column B while xlookup will pick just the first match of 41 within the column
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论