Hi guys,
I've scoured the web but can't find an easy way to get around this, hope you can help.
I have two columns of data - a code in 012345-1 format and a location in 01-02-03 format. The latter is NOT a date, it just happens to be in the same format.
I'm using a Vlookup formula to return the location in a new sheet but it keeps switching the first two pairs of numbers around as if it's a date ie 01-02-03 becomes 02-01-03.
I have tried changing the format of the cells on both sheets to everything in the list including special and custom and I've tried changing the date setting of the sheet to 1904 system - all to no avail. I just can't convince Excel that the location is NOT a date.
Can anyone help?
you could try wrap the TEXT function around your vlookup.
it should return your lookup as a text string in the "00-00-00" format then.=TEXT(VLOOKUP(A1,table1,colno,FALSE),"00-00-00")
2+2=5 for extremely large values of 2.
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
"0##\-0##\-0##"
2+2=5 for extremely large values of 2.
Thanks Ian
=TEXT(VLOOKUP(J9,'[other-worksheet.xlsx]Sheet1'!$D$3:$I$1151,1,TRUE),"0##\-00##\-0##\")
It's returning - #VALUE!
With FALSE I'm getting #N/A
Sure this is something really obvious that I'm missing
could be the extra \ on the end of the format
2+2=5 for extremely large values of 2.
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?
Audere est facere
D'oh. Removed it and the error remains
The source cells are formatted as text, it's just that the data is in a date format.
Are you sure? If one of the source cells is C2 then what does this formula give youOriginally Posted by vicshippers
=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
Audere est facere
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.
Last edited by vicshippers; 12-09-2011 at 06:21 AM. Reason: Additional info added
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?
Audere est facere
I've managed to extrapolate an example, this should clear the fog for you!
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))&""
Audere est facere
You are wonderful. Thank you so much! Never used INDEX/MATCH...always learning!
Thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks