Hello Guys,

I wonder if you can help,

In Excel - I have two spreadsheets. I need to copy the values from Sheet 2 column date to sheet 1 column date.


Example:

Sheet 1 Sheet 2
col 1 | Date Col 1 | Date
a 02/03/2010 b 01/01/2010
b 01/01/2010 a 02/03/2010
c 03/03/2010 c 03/03/2010

Can any lookup functions achieve this? As you can see the data in Sheet 2 isnt sorted the same as sheet 1.

Thanks

Lucy

Hi Lucy,

So long as there is only one row of each date then, yes, you can use VLOOKUP() (or a similar combination of functions) to pull the data over.

How many rows and columns of data are there?

Would you like me to attach an example?

Hello Colin,

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.

Sheet 1

col1 col2
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]


Sheet 2

Col 1 Col 2
A37/890 | BRISTOL ROAD 11/05/2004
A37/930 | BRISTOL ROAD

Hi Lucy,

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.