VLOOKUP with Dates not working

[Lefty099]

Hi all, hopefully this will be a simple one for you.

I am looking for a formula that I can put in a table to do the following.

Sheet1 Column C has a list of my products. Column E contains the date the goods were received.
(For some reason, and it is unavoidable unfortunately, the report the data comes from on sheet 1 posts 2 rows for the same product with a true date and a 0 on the next row. I have put an example down below.


Sheet 2 Column B has a formula transferring the product name across from sheet1.

Sheet2 Column C should have the corresponding date from sheet1.

=VLOOKUP(B4,Sheet1!B1:e20000,Not Valid,FALSE) isnt working.

any suggestions would be greatly appreciated.

Sheet1

	C	D	E	F
1	Hammer		23/06/2015
2	Hammer		0
3	Wrench		17/01/2012
4	Wrench		0

<tbody>
</tbody>

Sheet2

	A	B	C	D
1		Hammer	23/06/15
2		Wrench	17/01/12
3
4

<tbody>
</tbody>

I have no VBA experience hence why I am chasing formulas to do this.

Thanks in advance.

 

[Trebor76]

Hi Lefty099,

Try putting this formula in cell C1 of Sheet2 and filling down as required:

=VLOOKUP(B1,Sheet1!C:E,3,FALSE)

Regards,

Robert

 

[Lefty099]

No luck Robert, still getting the dreaded. #NA

 

[Trebor76]

Strange it works for me

If the text in cell B1 is not exactly the same as in column C of Sheet1 it will return #N/A

Ensure there are no trailing spaces in each cell for column B of Sheet2 and column C of Sheet1.

 

[Lefty099]

From what I can gatherI need to put a query in to make sure the daye is above 01/01/1900 or something like that. Way over my head on this one.

Thanks

 

[Lefty099]

The text is a mirror image as I have it copying across from the another sheet. the data transfer across the sheets is automated..

 

[Lefty099]

I just went through and made sure all those columns are formatted to date, it is now returning me 0/01/1900 instead of the 12/02/2016 which is the actual date for that product.

With the example I pasted up the op I had the date in E1 then the 0 in E2, it is the other way round, 0 in E1, 23/06/15 in E2.

Sorry, my bad

 

[Trebor76]

That makes sense. The VLOOKUP is working - when there are multiple matches it returns the first value it finds which in this case is a zero. A zero formatted as a date returns 01/01/1900 as this is the starting date used by Excel.

Sorting the data in Sheet1 by the date column (column E) by largest to smallest should do the trick.

Robert

 

[B___P]

Hi Lefty099, give this a try

=INDEX(Sheet1!E:E, ROW(Sheet1!E1) + 1 * (ROWS($C$1:C1) - 1))

Following your example paste it in c1 and drag down.

where Sheet1!E:E is the column with data to pick
Sheet1!E1 is the first cell to pick data from
1 is the gap
$C$1 is the cell in target sheet where you first insert formula
ROWS($C$1:C1) it is the range whose rows help you picking 1 each n

Hope this helps you.

 

[Lefty099]

Works a treat. Thanks heaps.