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