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 1


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.
    – smbfml
    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?
    – smbfml
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.