How does vlookup parse a "numeric character" argument

[Oppressed1]

When you vlookup("1"... things get interesting. I devised a very very easy to follow example which shows my question/dilemma. 30 seconds tops for you to reproduce it
In A1 Type the 3 characters "1" and hit enter
In B1 Type the number 1 and hit enter
Copy A1 to A3.

B3=vlookup(a3,$a$1:$b$1,2,false)
B4=vlookup("1",$a$1:$b$1,2,false)

For fun, guess which ones succeed on the lookup, and which ones don't. Then read further.

B4 fails. No, it's not coercing a number from the argument, because if you copy B1 to A1, it still fails. B3 works.

Can anyone explain how things are being parsed/interpreted?

Now how about this: if you change A1 to
'1
would B4 work?

Here's another test for you: what happens when you type
="1"
in A1. Will B4 work then? I'll bet not everyone guessed those right. Pretty funky, huh?

Thanks if you can explain how things are being parsed/interpreted?

P.S. And I just have to say this: almost without fail (on OTHER boards, not THIS one ), whenever I post a confusing problem like this, that ought to be simple but apparently isn't, and someone is unable to actually answer, they get frustrated and feel compelled to explain that what I'm trying to do is ludicrous, or chastise that vlookup is supposed to be used for numeric lookups, etc. However I know that YOU are going to really explain what is happening here, and we'll all sing Kumbaya Or maybe it's just the simplest question in the world and I'm the only one who doesn't see it!

[MrShorty]

I am not sure I understand. It seems to make sense to me:

When I enter "1" into a cell, the cell contains that entire text string "1". This is different from the 1 text string, which is different from the number 1. In the VLOOKUP() formula, because " " is used to designate a text string argument (everything in between the quotes is text), the B4 version of the vLOOKUP() is searching for the text string 1, which is different from the text string "1". I am not sure how to get the VLOOKUP() to include the double quote characters in the search string, but I seem to recall seeing others use the CONCATENATE() function or operator to do this. For example, try =VLOOKUP(CONCATENATE(CHAR(34),1,CHAR(34)),A1:B1,2,FALSE) in B5.

Perhaps the confusion is around when is Excel using the double quote character as a text literal and when is it using the double quote character to identify that what is in between the double quote is a text string.

Does that help?

[Oppressed1]

I think that's a well developed answer. Works for me!

[Oppressed1]

In this case, concatenate is handy and compact (I like it when I remember to use it), but doesn't appear to be required to "fix" B4. You can use 3 quotes, 1, 3 quotes a la
vlookup("""1""", ...

[Oppressed1]

I will say that
A1="1"
is the most nonintuitive. It feels more like the 3 character representation. But, whadya know, it's LEN is 1. I'm not disagreeing with the reply given; just pointing out what appears mildly amusing to some of us