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.
>Solution :
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
