View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David Biddulph[_2_] David Biddulph[_2_] is offline
external usenet poster
 
Posts: 8,651
Default VLOOKUP function with embedded LEFT

The double unary minus is used (among other purposes) to convert a text
string into a number.

The left function LEFT(F2,3) returns a text string, so it might be "123"
-LEFT(F2,3) would then return the number -123
--LEFT(F2,3) would return the number 123.

In other places you'll see the double unary minus used to convert Boolean
TRUE and FALSE results to 1 and 0 respectively in a similar manner.
-TRUE returns -1, --TRUE returns 1.
-FALSE calculates -0 (which is 0), --FALSE returns 0.
--
David Biddulph


"SamB" wrote in message
...
Glenn, this worked as well. I have never seen -- used before. What
command
is that giving?

"Glenn" wrote:

cm wrote:
I have duplicated your error; the values in your lookup table in
sheet2,
a1.... are numbers; replace your formual as follows:

=VLOOKUP(VALUE(LEFT(F2,3)),Sheet2!A1:B335,2,FALSE)


or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)