Date format in a VLookup formula

[atol]

Hello,

I am trying to use 2 vlookup functions in the same cell (which is fine), but one of the datasource cells is a date; when I run the vlookup, the date gets changed to a number. Is there a way to fix that? I want to see the date, but at the same time I do not want to change the date format in the datasource since I wil be using the date for a calculation (so text format wouldn't work).
Here is my formula:

=(VLOOKUP(E3,Repricing,6,FALSE))&" - "&" Mat "&(VLOOKUP(E3,Repricing,13,FALSE))

The cell 13 (seconnd vlookup) is what is giving me trouble.

Any help is greatly appreciated !

Rgds,
atol

 

[unmarkedhelicopter]

Try : -

=VLOOKUP(E3,Repricing,6,FALSE)&" - "&" Mat "&Year(VLOOKUP(E3,Repricing,13,FALSE))&"/"&Month(VLOOKUP(E3,Repricing,13,FALSE))&"/"&Day(VLOOKUP(E3,Repricing,13,FALSE))

It's a bit long though, you 'may' want to look into a udf but that can impact upon performance.

 

[atol]

That's super ! it did work great....! A BIG help !

another questions, if my column 16 (below) is a percentage, the formula below is bringing in 0.025 instead of 2.5%... Just wandering if there is something to fix this issue.
="LIBOR"&" + "&VLOOKUP(1,Repricing,16,FALSE)

Again you helped me a lot !

regards.
atol

 

[unmarkedhelicopter]

seeing 0.025 is 2.5% you just want it reformated ?
Try :-

="LIBOR + "&(VLOOKUP(1,Repricing,16,FALSE)*100)&"%"

 

[atol]

fantastic ! Thanks a lot!