How do I create an equivalent VLOOKUP function using FIND?

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" <[email protected]> wrote in message
news:%[email protected]...
> 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"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > Hi Dan,
>> >
>> > May be...
>> >
>> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>> >
>> > where AA1 houses the lookup value.
>>
>>
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.

Nice!

--

HTH

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


"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.
>
> HTH
>
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=396384
>

Yes I found that too.....

Many thanks for your advice it is exactly what I needed - in fact it's
solved another problem I was having too. Because this method specifies the
array where the data required to be found is explicitly you don't need to
count the number of columns away (as you do on VLOOKUP). This is great
because some of the data I will be looking up is in a different column and
hence fewer columns seperate it from the data I want to return.

Using your method this is no longer an issue - many thanks.

"Bob Phillips" wrote:

> Sorry, I dropped a comma when changing to your ranges. Should be
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Here is a case sensitive version of VLOOKUP
> >
> > =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
> >
> > which is an array formula, so commit with Ctrl-SHift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "dan" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to create an equivalent search algorithm using FIND to produce
> > similar
> > > results to if I was using VLOOKUP.
> > >
> > > Basically VLOOKUP does everything I want to do but is not case specific
> > when
> > > looking through cell ranges. I need a case specific VLOOKUP.
> > >
> > > I have an array and wish to be able to search through for a value say
> 'a'
> > > (different to 'A') and then return the data in the 4th column from it.
> > >
> > > In VLOOKUP 'speak' I have
> > >
> > > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> > >
> > > Can anyone help?
> >
> >
>
>
>

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi Dan,
> >
> > May be...
> >
> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
> >
> > where AA1 houses the lookup value.
>
>

Sorry, I dropped a comma when changing to your ranges. Should be

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))

--

HTH

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a case sensitive version of VLOOKUP
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
>
> which is an array formula, so commit with Ctrl-SHift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "dan" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to create an equivalent search algorithm using FIND to produce
> similar
> > results to if I was using VLOOKUP.
> >
> > Basically VLOOKUP does everything I want to do but is not case specific
> when
> > looking through cell ranges. I need a case specific VLOOKUP.
> >
> > I have an array and wish to be able to search through for a value say
'a'
> > (different to 'A') and then return the data in the 4th column from it.
> >
> > In VLOOKUP 'speak' I have
> >
> > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> >
> > Can anyone help?
>
>

Here is a case sensitive version of VLOOKUP

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))

which is an array formula, so commit with Ctrl-SHift-Enter

--

HTH

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


"dan" <[email protected]> wrote in message
news:[email protected]...
> I wish to create an equivalent search algorithm using FIND to produce
similar
> results to if I was using VLOOKUP.
>
> Basically VLOOKUP does everything I want to do but is not case specific
when
> looking through cell ranges. I need a case specific VLOOKUP.
>
> I have an array and wish to be able to search through for a value say 'a'
> (different to 'A') and then return the data in the 4th column from it.
>
> In VLOOKUP 'speak' I have
>
> =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
>
> Can anyone help?

Sorry, I dropped a comma when changing to your ranges. Should be

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))

--

HTH

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a case sensitive version of VLOOKUP
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
>
> which is an array formula, so commit with Ctrl-SHift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "dan" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to create an equivalent search algorithm using FIND to produce
> similar
> > results to if I was using VLOOKUP.
> >
> > Basically VLOOKUP does everything I want to do but is not case specific
> when
> > looking through cell ranges. I need a case specific VLOOKUP.
> >
> > I have an array and wish to be able to search through for a value say
'a'
> > (different to 'A') and then return the data in the 4th column from it.
> >
> > In VLOOKUP 'speak' I have
> >
> > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> >
> > Can anyone help?
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.

Here is a case sensitive version of VLOOKUP

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))

which is an array formula, so commit with Ctrl-SHift-Enter

--

HTH

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


"dan" <[email protected]> wrote in message
news:[email protected]...
> I wish to create an equivalent search algorithm using FIND to produce
similar
> results to if I was using VLOOKUP.
>
> Basically VLOOKUP does everything I want to do but is not case specific
when
> looking through cell ranges. I need a case specific VLOOKUP.
>
> I have an array and wish to be able to search through for a value say 'a'
> (different to 'A') and then return the data in the 4th column from it.
>
> In VLOOKUP 'speak' I have
>
> =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
>
> Can anyone help?

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi Dan,
> >
> > May be...
> >
> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
> >
> > where AA1 houses the lookup value.
>
>

Nice!

--

HTH

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


"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.
>
> HTH
>
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=396384
>

Yes I found that too.....

Many thanks for your advice it is exactly what I needed - in fact it's
solved another problem I was having too. Because this method specifies the
array where the data required to be found is explicitly you don't need to
count the number of columns away (as you do on VLOOKUP). This is great
because some of the data I will be looking up is in a different column and
hence fewer columns seperate it from the data I want to return.

Using your method this is no longer an issue - many thanks.

"Bob Phillips" wrote:

> Sorry, I dropped a comma when changing to your ranges. Should be
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Here is a case sensitive version of VLOOKUP
> >
> > =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
> >
> > which is an array formula, so commit with Ctrl-SHift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "dan" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to create an equivalent search algorithm using FIND to produce
> > similar
> > > results to if I was using VLOOKUP.
> > >
> > > Basically VLOOKUP does everything I want to do but is not case specific
> > when
> > > looking through cell ranges. I need a case specific VLOOKUP.
> > >
> > > I have an array and wish to be able to search through for a value say
> 'a'
> > > (different to 'A') and then return the data in the 4th column from it.
> > >
> > > In VLOOKUP 'speak' I have
> > >
> > > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> > >
> > > Can anyone help?
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > Hi Dan,
>> >
>> > May be...
>> >
>> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>> >
>> > where AA1 houses the lookup value.
>>
>>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > Hi Dan,
>> >
>> > May be...
>> >
>> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>> >
>> > where AA1 houses the lookup value.
>>
>>
>
>

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi Dan,
> >
> > May be...
> >
> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
> >
> > where AA1 houses the lookup value.
>
>

Here is a case sensitive version of VLOOKUP

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))

which is an array formula, so commit with Ctrl-SHift-Enter

--

HTH

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


"dan" <[email protected]> wrote in message
news:[email protected]...
> I wish to create an equivalent search algorithm using FIND to produce
similar
> results to if I was using VLOOKUP.
>
> Basically VLOOKUP does everything I want to do but is not case specific
when
> looking through cell ranges. I need a case specific VLOOKUP.
>
> I have an array and wish to be able to search through for a value say 'a'
> (different to 'A') and then return the data in the 4th column from it.
>
> In VLOOKUP 'speak' I have
>
> =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
>
> Can anyone help?

Nice!

--

HTH

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


"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.
>
> HTH
>
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=396384
>

Yes I found that too.....

Many thanks for your advice it is exactly what I needed - in fact it's
solved another problem I was having too. Because this method specifies the
array where the data required to be found is explicitly you don't need to
count the number of columns away (as you do on VLOOKUP). This is great
because some of the data I will be looking up is in a different column and
hence fewer columns seperate it from the data I want to return.

Using your method this is no longer an issue - many thanks.

"Bob Phillips" wrote:

> Sorry, I dropped a comma when changing to your ranges. Should be
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Here is a case sensitive version of VLOOKUP
> >
> > =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
> >
> > which is an array formula, so commit with Ctrl-SHift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "dan" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to create an equivalent search algorithm using FIND to produce
> > similar
> > > results to if I was using VLOOKUP.
> > >
> > > Basically VLOOKUP does everything I want to do but is not case specific
> > when
> > > looking through cell ranges. I need a case specific VLOOKUP.
> > >
> > > I have an array and wish to be able to search through for a value say
> 'a'
> > > (different to 'A') and then return the data in the 4th column from it.
> > >
> > > In VLOOKUP 'speak' I have
> > >
> > > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> > >
> > > Can anyone help?
> >
> >
>
>
>

Sorry, I dropped a comma when changing to your ranges. Should be

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))

--

HTH

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a case sensitive version of VLOOKUP
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
>
> which is an array formula, so commit with Ctrl-SHift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "dan" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to create an equivalent search algorithm using FIND to produce
> similar
> > results to if I was using VLOOKUP.
> >
> > Basically VLOOKUP does everything I want to do but is not case specific
> when
> > looking through cell ranges. I need a case specific VLOOKUP.
> >
> > I have an array and wish to be able to search through for a value say
'a'
> > (different to 'A') and then return the data in the 4th column from it.
> >
> > In VLOOKUP 'speak' I have
> >
> > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> >
> > Can anyone help?
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi Dan,
> >
> > May be...
> >
> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
> >
> > where AA1 houses the lookup value.
>
>

Here is a case sensitive version of VLOOKUP

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))

which is an array formula, so commit with Ctrl-SHift-Enter

--

HTH

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


"dan" <[email protected]> wrote in message
news:[email protected]...
> I wish to create an equivalent search algorithm using FIND to produce
similar
> results to if I was using VLOOKUP.
>
> Basically VLOOKUP does everything I want to do but is not case specific
when
> looking through cell ranges. I need a case specific VLOOKUP.
>
> I have an array and wish to be able to search through for a value say 'a'
> (different to 'A') and then return the data in the 4th column from it.
>
> In VLOOKUP 'speak' I have
>
> =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
>
> Can anyone help?

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" <[email protected]> wrote in message
news:%[email protected]...
> 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"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > Hi Dan,
>> >
>> > May be...
>> >
>> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>> >
>> > where AA1 houses the lookup value.
>>
>>
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.

Nice!

--

HTH

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


"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.
>
> HTH
>
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=396384
>

Yes I found that too.....

Many thanks for your advice it is exactly what I needed - in fact it's
solved another problem I was having too. Because this method specifies the
array where the data required to be found is explicitly you don't need to
count the number of columns away (as you do on VLOOKUP). This is great
because some of the data I will be looking up is in a different column and
hence fewer columns seperate it from the data I want to return.

Using your method this is no longer an issue - many thanks.

"Bob Phillips" wrote:

> Sorry, I dropped a comma when changing to your ranges. Should be
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Here is a case sensitive version of VLOOKUP
> >
> > =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
> >
> > which is an array formula, so commit with Ctrl-SHift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "dan" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to create an equivalent search algorithm using FIND to produce
> > similar
> > > results to if I was using VLOOKUP.
> > >
> > > Basically VLOOKUP does everything I want to do but is not case specific
> > when
> > > looking through cell ranges. I need a case specific VLOOKUP.
> > >
> > > I have an array and wish to be able to search through for a value say
> 'a'
> > > (different to 'A') and then return the data in the 4th column from it.
> > >
> > > In VLOOKUP 'speak' I have
> > >
> > > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> > >
> > > Can anyone help?
> >
> >
>
>
>

Sorry, I dropped a comma when changing to your ranges. Should be

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))

--

HTH

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a case sensitive version of VLOOKUP
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
>
> which is an array formula, so commit with Ctrl-SHift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "dan" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to create an equivalent search algorithm using FIND to produce
> similar
> > results to if I was using VLOOKUP.
> >
> > Basically VLOOKUP does everything I want to do but is not case specific
> when
> > looking through cell ranges. I need a case specific VLOOKUP.
> >
> > I have an array and wish to be able to search through for a value say
'a'
> > (different to 'A') and then return the data in the 4th column from it.
> >
> > In VLOOKUP 'speak' I have
> >
> > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> >
> > Can anyone help?
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > Hi Dan,
> >
> > May be...
> >
> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
> >
> > where AA1 houses the lookup value.
>
>

Nice!

--

HTH

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


"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Dan,
>
> May be...
>
> =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>
> where AA1 houses the lookup value.
>
> HTH
>
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=396384
>

Yes I found that too.....

Many thanks for your advice it is exactly what I needed - in fact it's
solved another problem I was having too. Because this method specifies the
array where the data required to be found is explicitly you don't need to
count the number of columns away (as you do on VLOOKUP). This is great
because some of the data I will be looking up is in a different column and
hence fewer columns seperate it from the data I want to return.

Using your method this is no longer an issue - many thanks.

"Bob Phillips" wrote:

> Sorry, I dropped a comma when changing to your ranges. Should be
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Here is a case sensitive version of VLOOKUP
> >
> > =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
> >
> > which is an array formula, so commit with Ctrl-SHift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "dan" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to create an equivalent search algorithm using FIND to produce
> > similar
> > > results to if I was using VLOOKUP.
> > >
> > > Basically VLOOKUP does everything I want to do but is not case specific
> > when
> > > looking through cell ranges. I need a case specific VLOOKUP.
> > >
> > > I have an array and wish to be able to search through for a value say
> 'a'
> > > (different to 'A') and then return the data in the 4th column from it.
> > >
> > > In VLOOKUP 'speak' I have
> > >
> > > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> > >
> > > Can anyone help?
> >
> >
>
>
>

Sorry, I dropped a comma when changing to your ranges. Should be

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a",$A$2:$A$3000),0))

--

HTH

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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a case sensitive version of VLOOKUP
>
> =INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))
>
> which is an array formula, so commit with Ctrl-SHift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "dan" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to create an equivalent search algorithm using FIND to produce
> similar
> > results to if I was using VLOOKUP.
> >
> > Basically VLOOKUP does everything I want to do but is not case specific
> when
> > looking through cell ranges. I need a case specific VLOOKUP.
> >
> > I have an array and wish to be able to search through for a value say
'a'
> > (different to 'A') and then return the data in the 4th column from it.
> >
> > In VLOOKUP 'speak' I have
> >
> > =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
> >
> > Can anyone help?
>
>

Here is a case sensitive version of VLOOKUP

=INDEX($D$2:$D$3000,MATCH(TRUE,EXACT("a"$A$2:$A$3000),0))

which is an array formula, so commit with Ctrl-SHift-Enter

--

HTH

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


"dan" <[email protected]> wrote in message
news:[email protected]...
> I wish to create an equivalent search algorithm using FIND to produce
similar
> results to if I was using VLOOKUP.
>
> Basically VLOOKUP does everything I want to do but is not case specific
when
> looking through cell ranges. I need a case specific VLOOKUP.
>
> I have an array and wish to be able to search through for a value say 'a'
> (different to 'A') and then return the data in the 4th column from it.
>
> In VLOOKUP 'speak' I have
>
> =VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).
>
> Can anyone help?

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" <[email protected]> wrote in message
news:%[email protected]...
> 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"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > Hi Dan,
>> >
>> > May be...
>> >
>> > =LOOKUP(9.9999999999E+307,FIND(AA1,A2:A3000),Z2:Z3000)
>> >
>> > where AA1 houses the lookup value.
>>
>>
>
>

I wish to create an equivalent search algorithm using FIND to produce similar
results to if I was using VLOOKUP.

Basically VLOOKUP does everything I want to do but is not case specific when
looking through cell ranges. I need a case specific VLOOKUP.

I have an array and wish to be able to search through for a value say 'a'
(different to 'A') and then return the data in the 4th column from it.

In VLOOKUP 'speak' I have

=VLOOKUP(a, $A$2:$Z:3000, 4, FALSE).

Can anyone help?