Excel Lookup and Where formula

[primagic]

I have a spreadsheet that has to columns A & B with information in them. A has a list of numbers and B has codes.

A B
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE

I have another column (C) that has the same information as B but there is more than 1 instance for each code

C
AAA
AAA
CCC
DDD
DDD
EEE

What I need to do in column D is lookup column B and get the value from column A. So my result would be:

C D
AAA 1
AAA 1
CCC 3
DDD 4
DDD 4
EEE 5

Any ideas how to achieve this in excel?

 

[xlhelp]

If your range is A1:C5, then
cell D1 would have the formula:
=LOOKUP(C1,$B$1:$B$5,$A$1:$A$5)


I don't need Google, my family knows everything

 

[arthurbr]

Also try =VLOOKUP(C1,CHOOSE({1,2},$B$1:$B$5,$A$1:$A$5),2,0) which does not depend on the lookup range being sorted

 

[SkipVought]

or, regardless of how many are in your table...
[tt]
=index($A:$A,match(C1,$B:$B,0),1)
[/tt]
and copy down.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}