Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Vlookup #N/A error due to formatting | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions | |||
vlookup. | Excel Worksheet Functions |