Who can explain this LOOKUP formula?

[Basz]

I posted a question on the Excel Worksheet Functions forum the other da
and received a formula that works just fine. However, I don't understan
why it does what it does...

Can anyone explain me why this formula works?

=LOOKUP(2;1/(MID(A2;ROW(INDIRECT("1:1024"));1)=

");ROW(INDIRECT("1:1024")))

In a text string in cell A2 it finds the last space character an
returns its position, so I can extract the last word from the string i
A2.

Thanks anybody for helping me out.
Bas

 

[Dick Kusleika]

Basz

MID(A2;ROW(INDIRECT("1:1024"));1)=" "

This part will return 1,024 Trues and Falses; True if the character is a
space and False if it isn't. 1,204 is used because it's a large number that
will cover the length of any string you have in there.

In Excel, True is numerically equivalent to 1 and False is numerically
equivalent to zero. when you divide that whole array of Trues and False
by1, you get a bunch of #Div/0 errors and few 1's (1 for every space in the
string). That leaves you with an array like

{Div0, Div0, Divo, 1, Div0, etc...}

This array is called the LookupVector (see LOOKUP in help). The largest
number in the LookupVector will be 1 (everything else will be a Div0 error).
The LookupVector needs to be sorted, and since we look for 2, it will find
the largest value in the vector that doesn't exceed 2. That means it will
find the last 1 in the array.

The ResultVector is a bunch of sequential numbers. You could enter the
numbers 1 - 1024 as an array, but it's easier to use the ROW(INDIRECT())
construct because it will return an array of those numbers.

It finds the last 1 in the LookupVector and takes the value from the same
position in the ResultVector. So if the last 1 in the LookupVector is in
the 14th position, it will take the 14th value from ResultVector. Since
ResultVector is an array of sequential numbers, the value will be 14.

That's about as good as I can do. I hope it's helpful.

 

[Jim Rech]

I'll try..

-The ROW function returns an array of integers from 1 to 1024.
-The MID function uses it to return an array of the characters in the A2
string, "a","b","c", etc.
-The comparison of the letters returned by the MID function to " " returns
an array of TRUE and FALSE values. TRUEs occur where the matching character
in the A2 string is a space.
-Dividing 1 by the array of TRUEs and FALSEs returns an array of 1's and
Errors. This is because 1/TRUE = 1 and 1/FALSE = Error.
-The LOOKUP function tries to find a 2 in the array of 1's and Errors.
Since it cannot find a match it returns the position of the highest value
below 2. This is the position of the last space.
-The LOOKUP function takes the position of the last space and return the
number found in that position in the second 1:1024 array.

Very neat!


Jim Rech
Excel MVP
|I posted a question on the Excel Worksheet Functions forum the other day
| and received a formula that works just fine. However, I don't understand
| why it does what it does...
|
| Can anyone explain me why this formula works?
| > =LOOKUP(2;1/(MID(A2;ROW(INDIRECT("1:1024"));1)="
| ");ROW(INDIRECT("1:1024")))
|
| In a text string in cell A2 it finds the last space character and
| returns its position, so I can extract the last word from the string in
| A2.
|
| Thanks anybody for helping me out.
| Basz
|
|
| ---
| Message posted
|

 

[JE McGimpsey]

The element

ROW(INDIRECT("1:1024")

returns an 1024 element array of numbers from 1 to 1024.

(MID(A2;ROW(INDIRECT("1:1024"));1)=" ")

will compare the first 1024 characters in A2 to the space character, and
return an array of 1024 TRUE/FALSE values. Note that all values after
the number of characters in A2 will be FALSE.

1/(MID(...))

will coerce the TRUE/FALSE to 1/0, and produce an 1024 element array of
1s or #DIV/0s.

LOOKUP(2;1/(MID(...));ROW(...))

will look for the largest value in the second element that is less than
or equal to 2 - which will be the last 1 value. It will then return the
value in the ROW(...) array corresponding to that 1 value, which will be
the position of the last space character in A2.

 

[JE McGimpsey]

I'd amend this to

1,204 is used because it's a large number that will *LIKELY* cover the
length of any string you have in there

since a cell can contain a string of 32767 characters.

 

[Basz]

Thanks to all of you who replied. Makes me feel very humble to b
confronted with this level of expertise...


MID(A2;ROW(INDIRECT("1:1024"));1)=" "