Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Text and Date for VLOOKUP
I have been trying without success to combine a text string and date into a lookup value. The lookup value will then be used in VLOOKUP to get a value from another sheet. My lookup value is a combination of three elements, a number that is in a text formatted cell, a hyphen and a number representation of a date. The values for the fields are as follows: Text cell - 1013116 Date cell - 14-Dec-05, which is represented as the number 38700. The two cells are combined with a hyphen in the middle. I use the CONCATENATE function to merge the cells together. I use the same formula on the second sheet to derive the the first column of the lookup range. The cell contents for both sheets appear the same. Both cells have the same cell format, TEXT. The VLOOKUP does not work. There are not errors, VLOOKUP returns the last value in the range indicating that there was no match. What am I doing wrong????? -- biggymismyname ------------------------------------------------------------------------ biggymismyname's Profile: http://www.excelforum.com/member.php...o&userid=29363 View this thread: http://www.excelforum.com/showthread...hreadid=490746 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Text and Date for VLOOKUP
Use exact match in the lookup
=vlookup(a1,b1:c200,2,0) as an example However you probably will not find a match at all using your method, what are the values in the lookup table that you are trying to lookup how does the concatenated formula look (and the result of it)? -- Regards, Peo Sjoblom "biggymismyname" <biggymismyname.1zkp7m_1133796304.9984@excelforu m-nospam.com wrote in message news:biggymismyname.1zkp7m_1133796304.9984@excelfo rum-nospam.com... I have been trying without success to combine a text string and date into a lookup value. The lookup value will then be used in VLOOKUP to get a value from another sheet. My lookup value is a combination of three elements, a number that is in a text formatted cell, a hyphen and a number representation of a date. The values for the fields are as follows: Text cell - 1013116 Date cell - 14-Dec-05, which is represented as the number 38700. The two cells are combined with a hyphen in the middle. I use the CONCATENATE function to merge the cells together. I use the same formula on the second sheet to derive the the first column of the lookup range. The cell contents for both sheets appear the same. Both cells have the same cell format, TEXT. The VLOOKUP does not work. There are not errors, VLOOKUP returns the last value in the range indicating that there was no match. What am I doing wrong????? -- biggymismyname ------------------------------------------------------------------------ biggymismyname's Profile: http://www.excelforum.com/member.php...o&userid=29363 View this thread: http://www.excelforum.com/showthread...hreadid=490746 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Text and Date for VLOOKUP
Peo It worked, adding the true false validation returned the correct value. Thanks for the tip :) -- biggymismyname ------------------------------------------------------------------------ biggymismyname's Profile: http://www.excelforum.com/member.php...o&userid=29363 View this thread: http://www.excelforum.com/showthread...hreadid=490746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting data from a website changes text to date | Excel Discussion (Misc queries) | |||
Excel enters date as a text format | Excel Discussion (Misc queries) | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions | |||
& reference answering as Date Serial # not actual text | Excel Worksheet Functions |