Google sheets - copy adjacent cells if value is identical

Question

I have a table like this

I'd like to have as a result:

Selected letter	Number
B	1
D	4

I tried to compare the two firsts columns to retrieve the third one with no luck.

doubleunary · Accepted Answer · 2023-06-13 17:32:30Z

1

With the table of key-number pairs in columns A2:A and C2:C, you can look up the keys in column B2:B with this formula in cell D2:

=arrayformula( iferror( vlookup(B2:B, A2:C, columns(A2:C), false) ) )

answered Jun 13, 2023 at 17:32

doubleunary

15.4k8 gold badges21 silver badges56 bronze badges

For some reasons, I am only getting #error! when pasting this formula in my cells ; any idea what could go wrong ?
– Dave Loepr
Commented Jun 4 at 8:08
1

In the event your spreadsheet locale uses commas , as decimal separators, you will have to use semicolons ; as formula argument separators. In other words, replace all the commas in the formula with semicolons. See Set a spreadsheet’s location & calculation settings and Syntax differences in formulas by Ben Collins for more info.
– doubleunary
Commented Jun 4 at 9:01
Thank you, magic-like answer :)
– Dave Loepr
Commented Jun 4 at 11:27

Add a comment |

Blindspots · Accepted Answer · 2023-06-13 20:19:53Z

1

FILTER function combined with COUNTIF will work

=FILTER({A2:A,C2:C},COUNTIF(B2:B,A2:A))

answered Jun 13, 2023 at 20:13

Blindspots♦

7,1205 gold badges19 silver badges38 bronze badges

Add a comment |

2 Answers 2