Excel: the function vlookup don't recognize ~?

Question

Hi,
I need to use vlookup to to extract data from a table.
All works properly, however when I have the symbol "~" it don't recognize it and return the error #N/A.

To test it, I tried to put in 2 different cells the symbol ~. I used the formula =a2=d4 (a2 and d4 are the cells where I wrote ~) and obviously it returned the result "TRUE". However using the formula "=vlookup(d4;a2:b2;2;false)", it returned #N/A.
I'm using office 2003.
Is it a bug?

Answer 1

The tilde (~) character is a wildcard used by Excel, which tells Excel to look for a literal character instead of using it as an actual wildcard, so to make Excel find a tilde, you need to use a double tilde. Here's how to handle it:



=IF(D4=CHAR(126), VLOOKUP(D4&D4, A2:B2, 2, FALSE), VLOOKUP(D4, A2:B2, 2, FALSE))



CHAR(126) is the tilde character. If your lookup value is the tilde, the formula uses D4&D4 (double tilde: ~~) so Excel is forced to search for the character in your lookup table.



If you lookup value is not the tilde, then the false part of the IF statement simply performs the original VLOOKUP.

Answer 2

The single best and most efficient way is to do this is;



1) Add your lookup formula to a spare column (e.g Column "A") and allow the #N/A! to happen.



2) Now reference these cells from the required cells like this;



=IF(ISNA(A1),0,A1)



3) Hide Column "A" by selecting it and going to Format>Column>Hide

The second most efficient is probably like this;



Instead of;



=VLOOKUP("Dog",A1:D100,2,False)



Use;

=IF(COUNTIF(A1:A100,"Dog"),VLOOKUP("Dog",A1:D100,2,FALSE),0)

The least efficient method is like;



=IF(ISNA(VLOOKUP("Dog",A1:D100,2,FALSE)),0,VLOOKUP("Dog",A1:D100,2,FALSE))



If you don't like the Zeros showing you can hide them via Tools>Options>View - Zero values. Or, cell-by-cell with a Custom Format like: General;-General;







Hope i helped you :)

Answer 3

documents in cols A to D J1 is corporation to seek col A for In telephone E2 enter =IF($J$a million=A2, ROW(),"") replica down documents record F2 =IF(ROWS($a million:a million)>matter($E:$E), "",INDEX(A:A, adventure(SMALL($E:$E, ROWS($a million:a million)), $E:$E,0)) replica throughout three columns then duplicate all 4 cells down required quantity of lines (say 10, do no longer complication lines without a counsel will demonstrate as blanks)