I had modified the ranges to match some sample data I typed in when I tried
to disect the formulas.

I've really learned a lot by studying the replies on these newsgroups.
Thank you very much for your time.

Lewis

"Bob Phillips" wrote in message
...
The find will return an array of values depending upon whether it finds
the
value or not, 1 for matches, #VALUE for non-matches (BTW you need to
reduce
the range size if you want to evaluate the formula). It provides
case-sensitiveness by virtue of the FIND function.

The array of values is then used to LOOKUP the BigNumber in the
lookup_vector. LOOKUP returns an index to the largest number less than the
lookup value, and uses that to extract from the result_vector Z2:Z3000. As
the array only consists of 1 and #VALUE, the largest values less than or
euqla will be 1. That has made me just realise, you don't need BigNumber,
2
will do

=LOOKUP(2,FIND(AA1,A2:A3000),Z2:Z3000)

Much more economical :-)

This actually works slightly differently than my offering, as if there are
multiple matches in the lookup_vector, this formula returns the last, mine
returns the first.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lewis Clark" <lewis_clark_644 @ yahoo.com wrote in message
news:4oHMe.7333$Al5.5850@trnddc04...
Would you please explain the logic for this formula? I think I
understand
what it does, but not how it works.

It looks like FIND returns the position in the "A" range of the lookup
value, and then LOOKUP returns the corresponding value from the "Z"
range.

When I try to break out the FIND call by itself to follow the logic, I
just
get the #VALUE! error. Does FIND return a vector in this case that is
all
zeros except for the position of the lookup value?

Thanks in advance.


"Krishnakumar"

wrote in message
news:Krishnakumar.1twl30_1124273195.9814@excelforu m-nospam.com...

Hi Dan,

May be...

=LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3 000)

where AA1 houses the lookup value.