Vlookup issue

[uul]

Morning everyone -

Got a query - basically i am doing a simple vlookup against a date, however the output for the date is coded so it says '26-aug-2014, so when the vlookup tries to find it, because of the ' it returns n/a. Any simple way of getting around this?

 

[JAMIR]

Hello uul,

1st check the date format are same there. Otherwise specific date is not available for lookup.

Better upload sample file.

Zameer

 

[uul]

Here is a test file - sheet 1 is looking up sheet 2.

 

[Mike H..]

Hi,

The dates on Sheet 2 aren't dates, they're text that looks like a date. Select a cell on sheet 2 and look in the formula bar and you will clearly see that every date has an apostrophe in front of it.

To solve this put a 1 in an empty cell and select that cell, right click and copy. Now select all the dates on sheet 2, right click, paste special, select multiply and click OK. All the dates now turn into numbers which you can format to a date format of your choosing.

A standard VLOOKUP formula will now work:-

=VLOOKUP(B4,Sheet2!$A$3:$B$29,2,FALSE)

Note you will still get a lot of #N/A! errors because there is only 1 valid match so you may want to allow for that with a formula like this.

=IFERROR(VLOOKUP(B4,Sheet2!$A$3:$B$29,2,FALSE),"")

 

[Sathish KV]

Hi,

To convert a range to proper date format you can also use Text to Columns

Select the range -> Alt D + E -> Next -> Next -> Select Date - > Finish

 

[uul]

Thankyou Mike/Jamir and Satish for you help on this -

 

[Somendra Misra]

Hi uul,

Just another option:

Try below function in B3 of Sheet2 and copy down:

=VLOOKUP(DATEVALUE(A3),Sheet1!$B$4:$C$36,2,0)

Regards,

 

[uul]

Ideally I am looking for a solution that doesnt involve altering the dates on sheet 2 - i dont think this is possible in a formula!

 

[Mike H..]

Ideally I am looking for a solution that doesnt involve altering the dates on sheet 2 - i dont think this is possible in a formula!

Hi,

Yes it is possible without changing the dates on either sheet, like this

=VLOOKUP(TEXT(B4,"dd-mmm-yy"),Sheet2!$A$3:$B$29,2,FALSE)

 

[Somendra Misra]

@uul

The formula I provided will not alter your Sheet2 dates.

Regards,

 

[uul]

Mike -that solved it, cheers. Thanks Somendra, although it wont alter the dates, i didnt want to drag anything down, as although i only gave an example, i have around 15 tabs like this, so needed a function that would just ignore the ' on the dates. Thankyou for responding to this query everyone.