# Look up value in column in Google Sheets

I normally don't use spreadsheets in this way so please bare with my incompetence.

I want to match values from two columns, getting the row number from the second where it matches and combine this with a specific column to get that value.

To get the row number I use `=MATCH(A1; B1:B10; 0)` in cell X1. So to get the value of interest in column Y I write `=CONCAT("Y";MATCH(A1; B1:B10; 0)`. This only results to the cell containing value "Y1" and not the value in cell Y1, when in a third cell I write `=X1` I do get the value from cell Y1 which I would like to have directly in cell X1. I think CONCAT is the wrong function to use here but I can't come up with any better.

E: I'm in Sweden, hence; instead of, for separating parameters. Its horrible.

## 1 Answer

You can use `filter()` to get the value directly, like this:

`=filter(Y1:Y10; B1:B10 = A1)`

Note that this formula will return all values where the values match. To get just the first match, use `=+filter(Y1:Y10; B1:B10 = A1)`.

• Works exactly how I want it to, thanks! The column I'm comparing only contains unique values so its perfect. Commented May 28, 2021 at 7:10
• Yes, thank you. Accepted the answer!Don't know if I should post a new question for this or if I can keep asking here but if I want to compare the rows depending on current row I'd like to be able to write `=filter(Y1:Y10; B1:B10 = CONCAT(Z;ROW()))` But that isn't working, I feel `CONCAT` really isn't meant to be used like this but how should one go about to do that? Commented May 28, 2021 at 7:32
• I cannot decipher that. Please post a new question, and describe the problem rather than describing your attempted solution. See What is the XY problem? Commented May 28, 2021 at 7:55