2

If I type in this formula:

=vlookup(A1,LetterTable,2,False)

With A1 set to "?" (without the quotes), and LetterTable contains this:

a   a
b   b
c   c
...  ...
-   -
+   +
?   ?
!   !

It always gives me "a" (no quotes). Why is this?

Also, if I copy that into an array in vba and loop through it, comparing it to the spreadsheet value, it won't find it either.

Community
  • 1
  • 1
Arlen Beiler
  • 15,336
  • 34
  • 92
  • 135

1 Answers1

1

See http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx:

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.

rskar
  • 4,607
  • 25
  • 21
  • VBA: I guess I'll need to see some example code. Question mark, by itself, may be a synonym for the Print method (Debug.? becomes Debug.Print). In the context of the LIKE operator, it matches any one character. Beyond that, I can't think of anything. – rskar Jan 10 '11 at 22:11
  • See http://stackoverflow.com/questions/4630412/question-mark-in-spreadsheet-causes-problems-in-excel-vba – Arlen Beiler Jan 10 '11 at 23:34
  • I tried the following, and got the expected result: (1) Created CipherTable range in worksheet, as in 4630412; (2) Created a function QM similar to code in 4630412; (3) Entered a formula in the style of =QM(cell-ref). It worked fine. See 4630412 for details. – rskar Jan 14 '11 at 15:27