VLOOKUP : problems !

[Gilbert De Ceulaer]

The lookup-value sits in c39, and is "10" formatted as text.
The VLOOKUP-formula itself sits in k39, and does a check to see if the
one-but-last letter is not a "x", and reads
=VLOOKUP(IF(MID($C39;LEN($C39)-1;1)="x";LEFT($C39;LEN($C39)-2);+$C39);someplace;somecolumn;FALSE).
In the lookup-table "10" is present and formatted as text.
Nonetheless, I get #N/A as a result.

If I use
=VLOOKUP(IF(MID($C39;LEN($C39)-1;1)="x";LEFT($C39;LEN($C39)-2);"10");someplace;somecolumn;FALSE).
I also get #N/A

But, if I use
=VLOOKUP(IF(MID($C39;LEN($C39)-1;1)="x";LEFT($C39;LEN($C39)-2);VALUE(+$C39));someplace;somecolumn;FALSE)
=VLOOKUP(IF(MID($C39;LEN($C39)-1;1)="x";LEFT($C39;LEN($C39)-2);10);someplace;somecolumn;FALSE)
the thing works.

I repeat : In the lookup-table "10" is present and formatted as text.

For the uniformity of the formula's in column K, I can not use this
VALUE-form, because the other values in column C are not numbers.

What is wrong ?
Gilbert
(e-mail address removed)

 

[Guest]

Hi Gil,

Your post is a bit confusing to me. Does this syntax get you close to what
you are trying to do?

=IF(MID(C39,LEN(C39)-1,1)<>"x",VLOOKUP(LEFT(C39,2),range,column,FALSE))

 

[Gilbert De Ceulaer]

Sorry Ken,
I made some stupid mistake in my initial question.
I'll rephrase it :

The lookup-value sits in c39, and is "10" formatted as text.
The VLOOKUP-formula itself sits in k39, and reads
=VLOOKUP($C39;someplace;somecolumn;FALSE).
In the lookup-table "10" is present and formatted as text.
Nonetheless, I get #N/A as a result.

If I use
=VLOOKUP("10";someplace;somecolumn;FALSE).
I also get #N/A

But, if I use
=VLOOKUP(VALUE(+$C39);someplace;somecolumn;FALSE)
or
=VLOOKUP(10;someplace;somecolumn;FALSE)
the thing works.

I repeat : In the lookup-table "10" is present and formatted as text.

For the uniformity of the formula's in column K, I can not use this
VALUE-form, because the other values in column C are not numbers.

What is wrong ?
Gilbert
(e-mail address removed)

 

[Guest]

If you haven't already done this, highlight column C. Then go to Data > Text
to Columns... > Next > Next and select Text
as the data format and then Finish. Sometimes that forces Excel to "see" the
entry as text.

 

[Gilbert De Ceulaer]

Doesn't work, Ken, and it disturbes other LOOKUP's
Gilbert

 

[Guest]

Okay.....

In an empty cell type =ISTEXT(C39) to validate that Excel treats it as text.
In another cell type =LEN(C39) to test that the length is indeed 2.

Do the same tests on the "looked up" cell to insure that it is the same.

Maybe that will give us a clue?

 

[Gilbert De Ceulaer]

Bingo !
Although when checked with Format>Cells A80 (in "someplace"), indicated to
be "Text", it proved FALSE when I checked it with =ISTEXT(A80) .
Simply changing (actually it was "confirming") A80 with Format>Cells>Number
to "Text" was not enough, I had to retype the "10" after formatting it with
Format>Cells>Number.
Thanks,
Gilbert