Help with a lookup function that will give me multiple answers

[dunkyb]

Hi all!! happy new year.

I am having problems with trying to solve a problem with adding post codes to certain roads. I have one list with Road Names and Post Codes, And the other list conatins only roads but has 3 entrys for each road.

My Problem

I want to get the postcodes from list 1 (Roads & Post Codes) to List 2 (Roads with 3 entries for each roads). i tried to use =If(oldroads,newroads,G2,"NO")

I am probably doing the wrong thing and it maybe easier to do in VBA.

if you can help it would be most greatful

here is an example of my database's, i wnat the BEST PC to go into newroads post codes.

( oldroads )
A B C
StreetID Street Properties MaxOfBN PostCode FirstOfPC BestPC
2871 A20 BR76HF BR76HF
1 A21 BR29LS BR29LS
2 ABBEY LANE 31 31 BR31SB BR3 1SB BR31SB


(new roads)
A B C D
AINSDALE CLOSE Refuse 11 Wednesday [i want post codes here]
ALBANY MEWS MMC 3 Monday 2 [i want post codes here]
ALBANY MEWS Paper 1 Monday 2 "
ALBANY MEWS Refuse 7 Monday "
ALBANY ROAD MMC 3 Tuesday 1 "
ALBANY ROAD Paper 2 Tuesday 1 "
ALBANY ROAD Refuse 8 Tuesday "
ALBEMARLE PARK MMC 5 Monday 1 "
ALBEMARLE PARK Paper 4 Monday 1 "
ALBEMARLE PARK Refuse 2 Monday "

Thanks Dunky