Google-sheets – Google spreadsheet get value from cell

concatenategoogle 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.

Thanks in advance.

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

Best 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).

Related Topic