Vlookup
 

I have defined a vlookup, however in the cell instead of the applicable
result/value (text or number) the Vlookup formula is displayed. I'am unable
to figure out what is the problem

I tried by inputting the Summation formula but again the display is the
Formula instead of result.

You could be in Formula Auditing mode. From the Tools menu, Formula
Auditing, Formula Audting Mode. (or Shortcut key Ctrl+`)

The cell may be formatted as Text instead of, say, General.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Nelson" wrote in message
...
I have defined a vlookup, however in the cell instead of the applicable
result/value (text or number) the Vlookup formula is displayed. I'am
unable
to figure out what is the problem

I tried by inputting the Summation formula but again the display is the
Formula instead of result.

Or you might highlight the cell/column and then got Edit Clear All.
Re-enter the VLOOKUP function. That works for me at times.

"Nelson" wrote:

I have defined a vlookup, however in the cell instead of the applicable
result/value (text or number) the Vlookup formula is displayed. I'am unable
to figure out what is the problem

I tried by inputting the Summation formula but again the display is the
Formula instead of result.

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