what does --LEFT(A5,{1,2,3})) mean?

[psrs0810]

In my lookup formula I have =LOOKUP(1000,--LEFT(A5,{1,2,3})), what does the lookup vector saying?

 

[pgc01]

Hi

LEFT(A5,{1,2,3}) generates an array with the 3 results of the Left() function.

For ex., if the value is "12abc" you get the result {"1","12","12a"}

 

[Jonmo1]

See here for an example of it's usage. Post#8
http://www.mrexcel.com/forum/showthread.php?t=567604

Althoug I changed {1,2,3}
to
ROW(B$1:INDEX(B:B,LEN(A1)))

That produces an array of numbers from 1 to the # of Characters in A1.
So if A1 is 10 characters long, that would produce
{1,2,3,4,5,6,7,8,9,10}
If A1 is only 4 characters long, it would be {1,2,3,4}


Hope that helps.


Note, this is also an example of a circular reference...

 

[Scott Huish]

The -- is coercing numbers stored as text to actual numbers. So the formula is finding the first consecutive number in the string by being larger than the largest possible value 999.

Excel Workbook
	A	B
5	99a	99
6	1a2	1
7	9aa	9
Sheet1

 

[pgc01]

An equivalent formula would be:

=-LOOKUP(0,-LEFT(A5,{1,2,3})),

 

[Jonmo1]

An equivalent formula would be:

=-LOOKUP(0,-LEFT(A5,{1,2,3})),

OOOH!!! You are soooo sneaky

 

[psrs0810]

perfect - thanks everyone