Excel: Fill Black Cells based on Postcodes, Region or City

[SGMacro]

I have an incomplete address list with lost of blank cells under either postcodes, regions or cities.

If I input a list of UK Postcodes, Regions and City into a separate sheet within the same workbook how can I use excel to fill the blank cells within a separate worksheet within the same workbook by cross referencing the data?

 

[alansidman]

I would use the Index/Match functions to search for the missing items in the uploaded table. You will have to do this three times to look for Postcodes, Regions and Cities. You will have to embed the Index/Match in a if statement for the blank cells.

 

[SGMacro]

Thanks Alan,

Ive not used this feature before and have pulled together the following:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

but it only matches the exact value How would I get it to match the first 2, 3 or 4 elements of data within a cell because postcodes can vary in digits?

I have found the following code but not sure where to enter if within the above array and cannot get it to work:

<code style="margin: 0px; padding: 0px; font-style: inherit; line-height: 12px;">=LEFT(A2,1)&IF(AND(MID(A1,2,1)>="0",MID(A1,2,1)<="9"),"",MID(A1,2,1))

</code>