Hi There,
I've done quite a bit of researching on ways to extract multiple instances of address where they all have a unique number however I always only get one returned result.
My table looks like below (sorry for formatting can't access table maker at present):
A
Unique Number
B
Original Address
C
Original Postcode
D
Routed Postcodes In Order
E
Unique Number in Routed Order
F
Routed Full Address
G
Stop Number
All this spreadsheet is for is to take some jumbled addresses then copy a unique number and address over to E and F once the postcodes have been put in the right order in column D manually.
So the formula in column E should lookup each instance of a postcode in column C matching it against the value in column D, then put the value from column A in the matched row into column E.
So far I have tried this formula below however where there are multiple instances of postcodes in column D and subsequently column C I either get the first instance or the last instance when using either SMALL or LARGE any ideas how I can get all instances making sure each one is unique?
{=INDEX($A$1:$A$101,LARGE(IF(C$1:C$101=D2,ROW(C$1:C$101)),COUNTIF(C$1:C$101,D2)))}
Also using this formula below in column F however I have the same problem with it not picking up every instance of the postcodes...
=INDEX($A$2:$C$101,MATCH(E2,$A$2:$A$101,0),1)&", "&INDEX($A$2:$C$101,MATCH(E2,$A$2:$A$101,0),2)
Your help is greatly appreciated.
I've done quite a bit of researching on ways to extract multiple instances of address where they all have a unique number however I always only get one returned result.
My table looks like below (sorry for formatting can't access table maker at present):
A
Unique Number
B
Original Address
C
Original Postcode
D
Routed Postcodes In Order
E
Unique Number in Routed Order
F
Routed Full Address
G
Stop Number
All this spreadsheet is for is to take some jumbled addresses then copy a unique number and address over to E and F once the postcodes have been put in the right order in column D manually.
So the formula in column E should lookup each instance of a postcode in column C matching it against the value in column D, then put the value from column A in the matched row into column E.
So far I have tried this formula below however where there are multiple instances of postcodes in column D and subsequently column C I either get the first instance or the last instance when using either SMALL or LARGE any ideas how I can get all instances making sure each one is unique?
{=INDEX($A$1:$A$101,LARGE(IF(C$1:C$101=D2,ROW(C$1:C$101)),COUNTIF(C$1:C$101,D2)))}
Also using this formula below in column F however I have the same problem with it not picking up every instance of the postcodes...
=INDEX($A$2:$C$101,MATCH(E2,$A$2:$A$101,0),1)&", "&INDEX($A$2:$C$101,MATCH(E2,$A$2:$A$101,0),2)
Your help is greatly appreciated.
