vlookup and ~

From Excel Help on VLOOKUP

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.


You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.


The values in the first column of table_array can be text, numbers, or logical values.


Uppercase and lowercase text are equivalent.


I would suggest Excel does not recognise ~ as text, number or a logical value

Richard

Hi

There is data next to it that is linked, sorting is not an option.

vlookup will find everything except text that at "~" in it

thanks

Jquest said:

Hi guys

I have data on a sheet that as "~" exp: (~notht~125)
and for some reason excel can't find them why

Thank you

Click to expand...

Hi Jquest:

I do not have an answer for you as to why? -- however, I am going to share some of my observations with the use of the special character ~ in regards to its with the VLOOKUP function ...

This special character better be used with some caution -- to ensure that one does not get unexpected results.

Jquest said:

Hi guys

I have data on a sheet that as "~" exp: (~notht~125)
and for some reason excel can't find them why

Thank you

Click to expand...

~ has a special meaning which must be deactivated to have it as an odinary char...

The formula in G2 is:

=VLOOKUP(SUBSTITUTE(F2,"~","~~"),$B$2:$C$5,2,0)

Thanks for the help guys

Aladin Akyurek solved it for me

Thanks