Thanks Bob, Ron beat you to it.
Kevin
--
Kevin


"Bob Phillips" wrote:

Use something like

=VLOOKUP(SUBSTITUTE(G11,"~","~~"),I11:J14,2,FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"kmhnhsuk" wrote in message
...
Hi,
I am trying to perform a lookup function on tow columns contaning data,
the
data is text strings with the values '~' and '^' included in some cases
eg:

BCH^~BCH
BOS^~BOS^~5FW-BW Burnham W

When I use the VLOOKUP formula to compare the data, when the strings are
identical in both columns I get the '#N/A' value returned when I should
get
blank. I get the '#N/A' value returned even when the values are different
or
not found when I should just get the string returned.

I think that the characters '~' and '^' are causing the VLOOKUP function
to
behave differently. Is there something that I am not doing correctly here?

Many thanks

Kevin


--
Kevin