Re: Who can explain this LOOKUP formula?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 10:43:44 -0500

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. 

-- 
*** Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
"Basz >" <<Basz.1a747b@excelforum-nospam.com> wrote in message
news:Basz.1a747b@excelforum-nospam.com...
> I posted a question on the Excel Work*** 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 from http://www.ExcelForum.com/
>
Quantcast