VLOOKUP unwanted date formatting

you could try wrap the TEXT function around your vlookup.

Please Login or Register  to view this content.

it should return your lookup as a text string in the "00-00-00" format then.

i think the format in that code may not return what you need if the values have leading zeros
try replacing the last part with

Please Login or Register  to view this content.

could be the extra \ on the end of the format

Really you need the source data formatted as text rather than a date, then the VLOOKUP wouldn't change it. How is the source data derived?

Originally Posted by vicshippers

The source cells are formatted as text.....

Are you sure? If one of the source cells is C2 then what does this formula give you

=ISNUMBER(C2)

If the answer is TRUE then the cell is formatted as a date or number. I suspect the answer will be TRUE because VLOOKUP shouldn't change the result if it's text formatted

It's coming up as FALSE.

I've changed the formatting of every cell on the sheet to TEXT to make doubley sure, but still getting the same.

Is this the original VLOOKUP you are using?

=VLOOKUP(J9,'[other-worksheet.xlsx]Sheet1'!$D$3:$I$1151,1,TRUE)

Note that by using 1 as the "column index" argument you will be returning a value from the lookup range, column D. Is that the intention? TRUE as 4th argument also implies that '[other-worksheet.xlsx]Sheet1'!$D$3:$D$1151 is sorted ascending, is that the case?

If you are looking up G6 in column D and trying to return the corresponding value from column C then you can't use VLOOKUP usually (VLOOKUP can only be used to retrieve data from columns to the right). For this you need INDEX and MATCH, try this formula

=INDEX($C$3:$C$1151,MATCH(G6,$D$3:$D$1151,0))&""