G
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)
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)