Matching value in A to value in B to return value from C where A has duplicates

Question

I have data like:

A B C 1 1 hi 2 2 hello 3 3 howdy 3 4 hi 4 5 hi there 5 6 hello there 5 7 howdy 6 8 hi

A and B contain the same values, however A has some duplicates which need to be kept. B does not contain any duplicates or any other values not in A.

I want to do something like:

"What is C for A"

So, my data would look like:

A C 1 hi 2 hello 3 howdy 3 howdy 5 hi there 5 hi there 6 hello there

If there were no duplicates in A it would be simple, I could just sort A and B alphabetically and delete B. However, because of the duplicates in A the values in A shift down an extra row periodically compared to B and C so the data no longer lines up. About 50% of the value in A are duplicates.

I tried:

=if(B$2:B$6=A2,C2,error)

It worked perfectly for the first few values where there are no duplicates in A (so A - B - C are aligned) but then after the first duplicate it just returns error.

Answer 1

That is exactly what VLOOKUP does.

A   B   C           D
1   1   hi          hi
2   2   hello       hello
3   3   howdy       howdy
3   4   hi          howdy
4   5   hi there    hi
5   6   hello there hi there
5   7   howdy       hi there
6   8   hi          hello there
10  9   greetings   #N/A

Set the formula for D2 to =VLOOKUP(A2,B$2:C$10,2,FALSE()) and then fill down to D10.

Note: I added a tenth row in order to show that an error occurs when A is not in B.