Each row contains a date or contain blanks. I thought of using VLOOKUP but it mentioned something about only using data that has been sorted in ascending order. The data I am trying to match is sorted differently on sheet 2 compared to sheet 1.
A37/890 | BRISTOL ROAD [Insert date data from sheet 2 (inc blanks) when col 1 sheet 1 matches col 1 sheet 2]
A37/930 | BRISTOL ROAD [Insert date data from sheet 2 (inc blanks) when col 1 sheet 1 matches col 1 sheet 2]
Col 1 Col 2
A37/890 | BRISTOL ROAD 11/05/2004
A37/930 | BRISTOL ROAD
When using VLOOKUP(), the optimal approach is to use [range_lookup] = 1 (or TRUE) but this requires the data to be in ascending order. If you pass in 0 (or FALSE) then it will find an exact match irrelevant of the order of the data - the trade off is that the lookup is a touch slower. A lot of people prefer to use INDEX() and MATCH() for this, but the formula is more complicated, so I think that you're best off using VLOOKUP() here.
I've attached a simple example to show that it can be used when the data order is mixed up. The main restrictions with VLOOKUP() are that you can only lookup data to the right of the lookup column and that the lookup column index (eg. 2 for 2nd column) is a magic number: it won't adjust if you insert a column in the lookup table.