Index and Match with multiple text criteria

Hi,

I'm currently a little stumped with an Index/ Match formula to match two sets of data and return the most (ideally all) results.

The data I am using is publicly available data: it is House Price data with addresses and Energy Performance Certificate (EPC) data with addresses. The EPC data also contains floor areas (square metres) of the properties. Fundamentally, what I am trying to do is match the address from the House Price data with the addresses in the EPC data to return the floor areas in the EPC data.

Sounds fairly simple, and would be, however, the addresses are input slightly differently in each set of data and sometimes contain additional descriptions. I am therefore wanting to accurately match as much description from the addresses to match them correctly. In all instances, the post codes are always in the same format, so they are easily matched (as a starting point).

I will upload an excel spreadsheet shortly as an example.

Does this sound like something that can be achieved?


Many thanks

Geoff

Try this:
=INDEX(EPC[TOTAL_FLOOR_AREA],MATCH([@postcode],EPC[POSTCODE],0))

I did not make any manual checks as your sample sheet (which only needs to contain 10-20 rows) contains THOUSANDS of rows.

The reason I left so many records in was to then be able to do a thorough check to see if any formula ideas work.

That means that we would have to search through those records to find the one that matched (for instance) saon: Flat 301 paon: 33 and street: WITHY GROVE
We need to see only as many records from the EPC Data sheet and records on the Price Paid Data sheet (that they are known to match) a needed to understand why an accurate match is difficult.
Please manually fill in the expected values in the Area(m²) column so that we can have something against which to test our formulas/code
Let us know if you have any questions.