Perhaps the 10 in the table has a trailing space character.

What is the result if you type a 10 in cell C39, then in another cell
compare it to the 10 in the lookup table? For example, =C39=K1



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





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html