Matching two lists of postcodes

[Jamesb262]

Hi,

I have a list of postcodes that relate to actual addresses (list 1) and another list of postcodes that are broken down by which areas of the city they belong to (list 2)

I would like to be able to use a function to match/show which areas of the city postcodes from list 1 are in by comparing against list 2.

Can anyone help me with this?

I have attached a small example to show the type of data I have.

Thanks.

[63falcondude]

In C5:

=COUNTIF(Table1[Town Centre],TRIM(B5))

This will count the number of instances that the value in B5, for example, was found in the "Town Centre" column.

[AliGW]

Try this:

=NOT(ISNA(MATCH(TRIM([@Addresses]),Table1[Town Centre],0)))

I have had to use TRIM to clear the trailing spaces in the first list.

[davsth]

I fear your example is only part of the picture, your question says areas (plural)

If your list for each area of the city is a postcode in one column and a area in the next column, it will be much easier than columns headed with the area with the postcodes then below. Does the sample replicate reality

[Jamesb262]

Yes you are right, I think I have simplified the task somewhat by mistake.

I'm now realising that what I would like is for each residential postcode to be referenced against my table of area postcodes, where each column represents a different area in the city and the rows are the postcodes that belong in that area. Then once the postcode is matched the value returned would be which area of the city it belongs to.


I hope that makes sense

I have uploaded another example to show what I mean. How can I get the 'location' to populate with column headers in the postcode directory?

Thanks.

[Jamesb262]

Hi Ali,

Thanks for your response, its helpful though I think I have simplified what I am asking by mistake. Please see my post above to see better example

[AliGW]

Please manually fill in your expected results in the results table to show what we are aiming for.

[Jamesb262]

This would be the end result I am hoping for

[windknife]

Try this in E2.

Please Login or Register  to view this content.

copied down.

[AliGW]

Try this:

=LET(i,INDEX($I$1:$L$1,SUMPRODUCT(($I$2:$L$7=D2)*(COLUMN($I$2:$L$7)-8))),IF(COUNTA(i)>1,"-",i))

[63falcondude]

Another option, in E2:
=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($I$2:$L$7)/($I$2:$L$7=D2),1)),"-")

[Jamesb262]

Thanks so much for the help guys! I have managed to take the formula and copy to my data set. Amazed at how quick all the responses were and how useless at Excel I am

I know where to come when I inevitably hit another snag

[AliGW]

Which did you go with, out of interest, as you had three different ones.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Jamesb262]

I went with your solution in the end as you seem quite responsive if I were to have any further issues.

[AliGW]

LOL!!! Trying to curry favour, eh?

We are a very helpful bunch - all of us - so you will never be without assistance.

Always happy to help.