Match full postcode against list of partial postcodes

[TheRobsterUK]

I have a list of partial postcodes where we are carrying out monitoring of some field work. There are about 30 areas where this is happening but the scale is different, e.g. in some areas we are doing work in the postcode region LS3, so this includes ALL postcodes starting LS3 (e.g. LS3 4NT, LS3 2RJ etc). In some areas the work is more focused, e.g. it is in HD9 3, so would include HD9 3BX, HD9 3EF etc, but not HD9 4TS and so on

My problem is this: every week I get a list of ALL the jobs we've carried out and for each job the identifying location is the full postcode and for each of these I need to find the ones which are in the list of sites we are monitoring (we do jobs in far more areas than we are monitoring but I need to search what is common in both lists). However the issue is that the search terms aren't consistent in size or type, i.e. if the full postcode is HD3 9BX, I might for instance want to match it to HD3 areas, or I might want to be more specific and only match it to HD3 9 areas.

To give some examples, suppose this is the list of areas I am monitoring - these are the partial post codes I'm interested in:
BD3
BD4
LS12 4

I get a list of completed jobs, the postcodes of which are:
LS13 4BX
BD3 9RU
BD3 7KB
LS12 4BX
LS12 3ZT

I need a formula which takes each postcode from the completed list of jobs and determines which, if any, of the partial postcodes from the monitoring sites applies to it. So in the data above, LS13 4BX doesn't appear in the monitoring sites so would return nothing. BD3 appears twice (BD3 9RU and BD3 7KB) and LS12 4 appears once (LS12 4BX but not LS12 3ZT).

I'm trying to put a formula together that will pick out the correct partial postcode and match it to the full postcode. Ideally the value returned should be the correct partial postcode against the full postcode, e.g.
BD3 9RU = BD3
LS12 4BX = LS12 4
and so on...

Thanks in advance!

[BadlySpelledBuoy]

Could you put together a workbook and show your expected outcome (manually generated)?

BSB

[TheRobsterUK]

See attached, thx!

[63falcondude]

Try this in M4:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND(A$4:A$30,G4)),ROW(A$4:A$30)),1)),"no match") Ctrl Shift Enter

[samba_ravi]

or can try below formula
=IFERROR(INDEX($A:$A,LOOKUP(2,1/COUNTIF($L4,$A$4:$A$30&"*"),ROW($A$4:$A$30))),"no match")
or
=IFERROR(INDEX($A:$A,LOOKUP(2,1/MATCH($A$4:$A$30&"*",L4,0),ROW($A$4:$A$30))),"no match")

[victorkhube]

Victor Masingita Makhubele
50 mins
Afternoon, Experts

1. I would like to have a formula or code which will provide a sum of manhour without DI, meaning if each month manhour has a DI from 1 (as highlighted in red) the month's manhour must be exluded. The document must automatically get its total man hour without DI light higlighted in yellow.

2. The document must be rolling kind as monthly information is captured and runs for 12 months period. And if you add the current month the first month on the block must be moved out.

Thanks for your help. I am willing to pay if

[dominicb]

Good afternoon victorkhube

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[jtakw]

Hi,

This would also work, in M4 copied down:

Please Login or Register  to view this content.

See attached.

[FlameRetired]

Another way

Please Login or Register  to view this content.

[shukla.ankur281190]

May be much shorter can be

Please Login or Register  to view this content.

[jtakw]

Hi,

This would also work, in M4 copied down:

Please Login or Register  to view this content.

See attached.

May be much shorter can be

Please Login or Register  to view this content.

Don't know about "much shorter", basically the same formula...