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

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

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