VLOOKUP : problems !
 

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" wrote:

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

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" wrote:

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

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" wrote:

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

"Ken" wrote in message
...
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" wrote:

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