Lookup not returning data due to "~"

**dgo** · 02-01-2007, 06:02 AM

Hi,

I've got a spreadsheet that has a Hlookup into a different spreadsheet. It is looking up codes such as MSPACF(RI) and these work fine and it is returning data associated with these codes

The problem comes when I look up the code S&PCOMP(RI)~£ I have 4 codes that all end in ~£ and for each of these the lookup is returning N/A.

Is there anyway to get this work? It's driving me mad!!!

Many thanks

Dom.

**Carim** · 02-01-2007, 07:04 AM

Hi,

Have you tried ...
CHAR(163)

**dgo** · 02-01-2007, 07:11 AM

Hi Carim,

I have no idea what that means (though I am guessing CHAR(163) means character and relates to ~)

How would I pur S&PCOMP(RI)~£ in using CHAR(163)

Thanks!

**Carim** · 02-01-2007, 07:19 AM

Hi again,

I initially thought you were using Edit Replace ...
Could you share you lookup() function ...?

**dgo** · 02-01-2007, 07:37 AM

Hi,

The Hlookup I am using is

=HLOOKUP(A266,'J:\PMA UK New\Performa\Dynamic Data\Indices\[DataStream Download.xls]Sheet1'!$5:$11,3,FALSE)

Where A266 is the cell reference for S&PCOMP(RI)~£

I'll attach the files in a bit via a zip file, I am just waiting to IT to put win zip on my computer.

Thanks!!

**Dav** · 02-01-2007, 08:01 AM

Its a mystery to me, if the list is sorted ascending the lookup function works for the values, but if an exact match is required it fails. I have no idea why but it must be the character highlighed

Regards

Dav

**Bryan Hessey** · 02-01-2007, 08:41 AM

Originally Posted by dgo

Hi,

I've got a spreadsheet that has a Hlookup into a different spreadsheet. It is looking up codes such as MSPACF(RI) and these work fine and it is returning data associated with these codes

The problem comes when I look up the code S&PCOMP(RI)~£ I have 4 codes that all end in ~£ and for each of these the lookup is returning N/A.

Is there anyway to get this work? It's driving me mad!!!

Many thanks

Dom.

Hi,

perhaps

If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character

from
http://office.microsoft.com/en-us/ex...093351033.aspx

will explain?

=VLOOKUP("A~~A",A1:A3,1,FALSE)

finds A~A

---

**Dav** · 02-01-2007, 09:35 AM

with the guidance provided by Brian, you need to replace the ~ with another character and use this for the match function. I have used the @ character in my example. Preferably a character that is not used in the other names. then I would use the match function to rerun the values you were interested in. hopefully u can download the spreadsheet and see more clearly

otherwise if the value being looked up is in a9 the following needs to be entered as an array (shft ctrl enter)

=OFFSET($A$7,0,MATCH(SUBSTITUTE(A9,"~","@"),SUBSTITUTE($5:$5,"~","@"),0)-1)

Regards

Dav

**daddylonglegs** · 02-01-2007, 10:04 AM

As Bryan says, to search for a ~, or text containing a ~ you simply need to search for a "~~" therefore change your formula to

=HLOOKUP(SUBSTITUTE(A266,"~","~~"),'J:\PMA UK New\Performa\Dynamic Data\Indices\[DataStream Download.xls]Sheet1'!$5:$11,3,FALSE)

This will work equally well, whether your data contains ~ or not