Find largest alphanumeric value matching alpha criteria in databas

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

I really thought your MAX formula would work, but for some reason it didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need to be
able to input in a cell. Then the formula needs to look at that cell and
then go to the database to find all the records that also contain that same
description. Then among all the ones it finds that that description in one
field, return a number to me from another field... the highest number from
the matches.
So what I did was on my template (not my database, create a formula that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif Insert" or
whatever alphabetic description I need to match. In that same column there
will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
B5 is the cell on the template that I will input that same description.
Column C has the project numbers like 5068 or 5037 etc., and I need the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure why
it didn't work.
It returned a value of 0. It should have returned an actual job number from
my test entries.
"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

I have a template that sends info to a database. In that template, I need to
put in a function that will use alphabetic information in a cell as my
criteria. It then should look in the database to find records that match
that criteria and then send back to me the largest match from a field of the
record among the records that match the criteria.
I have tried several functions, the best of which were VLOOKUP and DMAX.
Neither was what I needed. VLOOKUP gives field from the first record of the
list that matches the criteria. (Instead, I need to look at all the ones
that match and return a field from the largest alphnumberic match). DMAX for
some reason doesn't work when the database is closed and it doesn't let me
specify criteria for a field.

Here's an example: the template is used to enter project information for
several clients. Each of those clients may do similar projects. Of those
projects and within each client, several of the projects may be similar.
Let's say I have two projects and need to find the most recent that was
similar to another project. Projects have project numbers that go higher as
they become more recent. In the template, I can have the information "Calif
Insert" which represents the client name and project type, and want the
function to go to the database to find all the records that have "Calif
Insert" in a specified column. Then find me from another specified field the
largest value in that field that matches "Client Insert" in the other field.
So the answer would be the job number which might look something like "5002"
which represents the most recent job (highest numbered job) that is also
described as "Calif Insert"
As I said, VLOOKUP was very close, but gave me the FIRST in the list that
matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
VLOOKUP doesn't have the ability to go past the first one it sees. Sorting
the database is not an option.
I am desparate. Any help would be appreciated.
Thanks.

Hi!

>(Instead, I need to look at all the ones that match and return a field from
>the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

> So the answer would be the job number which might look something like
> "5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I have a template that sends info to a database. In that template, I need
>to
> put in a function that will use alphabetic information in a cell as my
> criteria. It then should look in the database to find records that match
> that criteria and then send back to me the largest match from a field of
> the
> record among the records that match the criteria.
> I have tried several functions, the best of which were VLOOKUP and DMAX.
> Neither was what I needed. VLOOKUP gives field from the first record of
> the
> list that matches the criteria. (Instead, I need to look at all the ones
> that match and return a field from the largest alphnumberic match). DMAX
> for
> some reason doesn't work when the database is closed and it doesn't let me
> specify criteria for a field.
>
> Here's an example: the template is used to enter project information for
> several clients. Each of those clients may do similar projects. Of those
> projects and within each client, several of the projects may be similar.
> Let's say I have two projects and need to find the most recent that was
> similar to another project. Projects have project numbers that go higher
> as
> they become more recent. In the template, I can have the information
> "Calif
> Insert" which represents the client name and project type, and want the
> function to go to the database to find all the records that have "Calif
> Insert" in a specified column. Then find me from another specified field
> the
> largest value in that field that matches "Client Insert" in the other
> field.
> So the answer would be the job number which might look something like
> "5002"
> which represents the most recent job (highest numbered job) that is also
> described as "Calif Insert"
> As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> VLOOKUP doesn't have the ability to go past the first one it sees.
> Sorting
> the database is not an option.
> I am desparate. Any help would be appreciated.
> Thanks.

You're welcome. Thanks for the feedback!

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, Biff!
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>>
>> A couple of things:
>>
>> This is an array formula. An array formula cannot reference entire
>> columns,
>> D:D, C:C
>>
>> Reduce the range reference size:
>>
>> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>>
>> Do you really need to use the entire column as a reference?
>>
>> Also, since this is an array formula you MUST use the key combo of
>> CTRL,SHIFT,ENTER instead of just using ENTER.
>>
>> Biff
>>
>> "Alison" <[email protected]> wrote in message
>> news:[email protected]...
>> >I also just tried
>> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>> >
>> > which didn't work... returned the "N/A" which is my value if false.
>> > There
>> > should have been an actual job number returned from Column C.
>> >
>> > "Alison" wrote:
>> >
>> >> I really thought your MAX formula would work, but for some reason it
>> >> didnt.
>> >> In my scenario, the "Calif Insert" is a changing criteria that I need
>> >> to
>> >> be
>> >> able to input in a cell. Then the formula needs to look at that cell
>> >> and
>> >> then go to the database to find all the records that also contain that
>> >> same
>> >> description. Then among all the ones it finds that that description
>> >> in
>> >> one
>> >> field, return a number to me from another field... the highest number
>> >> from
>> >> the matches.
>> >> So what I did was on my template (not my database, create a formula
>> >> that
>> >> refers to the database and to a cell on the template:
>> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> >> Column D is the database column with the description like "Calif
>> >> Insert"
>> >> or
>> >> whatever alphabetic description I need to match. In that same column
>> >> there
>> >> will be other descriptions like "Harris Print Ad" or "First
>> >> Newsletter"
>> >> etc.
>> >> B5 is the cell on the template that I will input that same
>> >> description.
>> >> Column C has the project numbers like 5068 or 5037 etc., and I need
>> >> the
>> >> largest one of those records that matches the description in column D.
>> >>
>> >> Unless there's a problem with referencing a cell like B5, I'm not sure
>> >> why
>> >> it didn't work.
>> >> It returned a value of 0. It should have returned an actual job
>> >> number
>> >> from
>> >> my test entries.
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >(Instead, I need to look at all the ones that match and return a
>> >> > >field
>> >> > >from
>> >> > >the largest alphnumberic match).
>> >> >
>> >> > Can you post some examples of these "alpha-numerics" and how do you
>> >> > determine one is larger than another?
>> >> >
>> >> > Do you mean something like:
>> >> >
>> >> > 2001-10-B
>> >> > 2000-75-Z
>> >> >
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> >
>> >> > If 5002 is a real number:
>> >> >
>> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >> >
>> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >> >
>> >> > Biff
>> >> >
>> >> > "Alison" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >I have a template that sends info to a database. In that template,
>> >> > >I
>> >> > >need
>> >> > >to
>> >> > > put in a function that will use alphabetic information in a cell
>> >> > > as
>> >> > > my
>> >> > > criteria. It then should look in the database to find records
>> >> > > that
>> >> > > match
>> >> > > that criteria and then send back to me the largest match from a
>> >> > > field
>> >> > > of
>> >> > > the
>> >> > > record among the records that match the criteria.
>> >> > > I have tried several functions, the best of which were VLOOKUP and
>> >> > > DMAX.
>> >> > > Neither was what I needed. VLOOKUP gives field from the first
>> >> > > record
>> >> > > of
>> >> > > the
>> >> > > list that matches the criteria. (Instead, I need to look at all
>> >> > > the
>> >> > > ones
>> >> > > that match and return a field from the largest alphnumberic
>> >> > > match).
>> >> > > DMAX
>> >> > > for
>> >> > > some reason doesn't work when the database is closed and it
>> >> > > doesn't
>> >> > > let me
>> >> > > specify criteria for a field.
>> >> > >
>> >> > > Here's an example: the template is used to enter project
>> >> > > information
>> >> > > for
>> >> > > several clients. Each of those clients may do similar projects.
>> >> > > Of
>> >> > > those
>> >> > > projects and within each client, several of the projects may be
>> >> > > similar.
>> >> > > Let's say I have two projects and need to find the most recent
>> >> > > that
>> >> > > was
>> >> > > similar to another project. Projects have project numbers that go
>> >> > > higher
>> >> > > as
>> >> > > they become more recent. In the template, I can have the
>> >> > > information
>> >> > > "Calif
>> >> > > Insert" which represents the client name and project type, and
>> >> > > want
>> >> > > the
>> >> > > function to go to the database to find all the records that have
>> >> > > "Calif
>> >> > > Insert" in a specified column. Then find me from another
>> >> > > specified
>> >> > > field
>> >> > > the
>> >> > > largest value in that field that matches "Client Insert" in the
>> >> > > other
>> >> > > field.
>> >> > > So the answer would be the job number which might look something
>> >> > > like
>> >> > > "5002"
>> >> > > which represents the most recent job (highest numbered job) that
>> >> > > is
>> >> > > also
>> >> > > described as "Calif Insert"
>> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the
>> >> > > list
>> >> > > that
>> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> >> > > guess
>> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> >> > > Sorting
>> >> > > the database is not an option.
>> >> > > I am desparate. Any help would be appreciated.
>> >> > > Thanks.
>> >> >
>> >> >
>> >> >
>>
>>
>>

The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.

"Biff" wrote:

> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>

Thanks so much, Biff!


"Biff" wrote:

> Hi!
>
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>
> A couple of things:
>
> This is an array formula. An array formula cannot reference entire columns,
> D:D, C:C
>
> Reduce the range reference size:
>
> =MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))
>
> Do you really need to use the entire column as a reference?
>
> Also, since this is an array formula you MUST use the key combo of
> CTRL,SHIFT,ENTER instead of just using ENTER.
>
> Biff
>
> "Alison" <[email protected]> wrote in message
> news:[email protected]...
> >I also just tried
> > =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
> >
> > which didn't work... returned the "N/A" which is my value if false. There
> > should have been an actual job number returned from Column C.
> >
> > "Alison" wrote:
> >
> >> I really thought your MAX formula would work, but for some reason it
> >> didnt.
> >> In my scenario, the "Calif Insert" is a changing criteria that I need to
> >> be
> >> able to input in a cell. Then the formula needs to look at that cell and
> >> then go to the database to find all the records that also contain that
> >> same
> >> description. Then among all the ones it finds that that description in
> >> one
> >> field, return a number to me from another field... the highest number
> >> from
> >> the matches.
> >> So what I did was on my template (not my database, create a formula that
> >> refers to the database and to a cell on the template:
> >> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> >> Column D is the database column with the description like "Calif Insert"
> >> or
> >> whatever alphabetic description I need to match. In that same column
> >> there
> >> will be other descriptions like "Harris Print Ad" or "First Newsletter"
> >> etc.
> >> B5 is the cell on the template that I will input that same description.
> >> Column C has the project numbers like 5068 or 5037 etc., and I need the
> >> largest one of those records that matches the description in column D.
> >>
> >> Unless there's a problem with referencing a cell like B5, I'm not sure
> >> why
> >> it didn't work.
> >> It returned a value of 0. It should have returned an actual job number
> >> from
> >> my test entries.
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >(Instead, I need to look at all the ones that match and return a field
> >> > >from
> >> > >the largest alphnumberic match).
> >> >
> >> > Can you post some examples of these "alpha-numerics" and how do you
> >> > determine one is larger than another?
> >> >
> >> > Do you mean something like:
> >> >
> >> > 2001-10-B
> >> > 2000-75-Z
> >> >
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> >
> >> > If 5002 is a real number:
> >> >
> >> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >> >
> >> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >> >
> >> > Biff
> >> >
> >> > "Alison" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >I have a template that sends info to a database. In that template, I
> >> > >need
> >> > >to
> >> > > put in a function that will use alphabetic information in a cell as
> >> > > my
> >> > > criteria. It then should look in the database to find records that
> >> > > match
> >> > > that criteria and then send back to me the largest match from a field
> >> > > of
> >> > > the
> >> > > record among the records that match the criteria.
> >> > > I have tried several functions, the best of which were VLOOKUP and
> >> > > DMAX.
> >> > > Neither was what I needed. VLOOKUP gives field from the first record
> >> > > of
> >> > > the
> >> > > list that matches the criteria. (Instead, I need to look at all the
> >> > > ones
> >> > > that match and return a field from the largest alphnumberic match).
> >> > > DMAX
> >> > > for
> >> > > some reason doesn't work when the database is closed and it doesn't
> >> > > let me
> >> > > specify criteria for a field.
> >> > >
> >> > > Here's an example: the template is used to enter project information
> >> > > for
> >> > > several clients. Each of those clients may do similar projects. Of
> >> > > those
> >> > > projects and within each client, several of the projects may be
> >> > > similar.
> >> > > Let's say I have two projects and need to find the most recent that
> >> > > was
> >> > > similar to another project. Projects have project numbers that go
> >> > > higher
> >> > > as
> >> > > they become more recent. In the template, I can have the information
> >> > > "Calif
> >> > > Insert" which represents the client name and project type, and want
> >> > > the
> >> > > function to go to the database to find all the records that have
> >> > > "Calif
> >> > > Insert" in a specified column. Then find me from another specified
> >> > > field
> >> > > the
> >> > > largest value in that field that matches "Client Insert" in the other
> >> > > field.
> >> > > So the answer would be the job number which might look something like
> >> > > "5002"
> >> > > which represents the most recent job (highest numbered job) that is
> >> > > also
> >> > > described as "Calif Insert"
> >> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
> >> > > that
> >> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
> >> > > guess
> >> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> >> > > Sorting
> >> > > the database is not an option.
> >> > > I am desparate. Any help would be appreciated.
> >> > > Thanks.
> >> >
> >> >
> >> >
>
>
>

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" <[email protected]> wrote in message
news:[email protected]...
>I also just tried
> =(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))
>
> which didn't work... returned the "N/A" which is my value if false. There
> should have been an actual job number returned from Column C.
>
> "Alison" wrote:
>
>> I really thought your MAX formula would work, but for some reason it
>> didnt.
>> In my scenario, the "Calif Insert" is a changing criteria that I need to
>> be
>> able to input in a cell. Then the formula needs to look at that cell and
>> then go to the database to find all the records that also contain that
>> same
>> description. Then among all the ones it finds that that description in
>> one
>> field, return a number to me from another field... the highest number
>> from
>> the matches.
>> So what I did was on my template (not my database, create a formula that
>> refers to the database and to a cell on the template:
>> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
>> Column D is the database column with the description like "Calif Insert"
>> or
>> whatever alphabetic description I need to match. In that same column
>> there
>> will be other descriptions like "Harris Print Ad" or "First Newsletter"
>> etc.
>> B5 is the cell on the template that I will input that same description.
>> Column C has the project numbers like 5068 or 5037 etc., and I need the
>> largest one of those records that matches the description in column D.
>>
>> Unless there's a problem with referencing a cell like B5, I'm not sure
>> why
>> it didn't work.
>> It returned a value of 0. It should have returned an actual job number
>> from
>> my test entries.
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >(Instead, I need to look at all the ones that match and return a field
>> > >from
>> > >the largest alphnumberic match).
>> >
>> > Can you post some examples of these "alpha-numerics" and how do you
>> > determine one is larger than another?
>> >
>> > Do you mean something like:
>> >
>> > 2001-10-B
>> > 2000-75-Z
>> >
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> >
>> > If 5002 is a real number:
>> >
>> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
>> >
>> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>> >
>> > Biff
>> >
>> > "Alison" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I have a template that sends info to a database. In that template, I
>> > >need
>> > >to
>> > > put in a function that will use alphabetic information in a cell as
>> > > my
>> > > criteria. It then should look in the database to find records that
>> > > match
>> > > that criteria and then send back to me the largest match from a field
>> > > of
>> > > the
>> > > record among the records that match the criteria.
>> > > I have tried several functions, the best of which were VLOOKUP and
>> > > DMAX.
>> > > Neither was what I needed. VLOOKUP gives field from the first record
>> > > of
>> > > the
>> > > list that matches the criteria. (Instead, I need to look at all the
>> > > ones
>> > > that match and return a field from the largest alphnumberic match).
>> > > DMAX
>> > > for
>> > > some reason doesn't work when the database is closed and it doesn't
>> > > let me
>> > > specify criteria for a field.
>> > >
>> > > Here's an example: the template is used to enter project information
>> > > for
>> > > several clients. Each of those clients may do similar projects. Of
>> > > those
>> > > projects and within each client, several of the projects may be
>> > > similar.
>> > > Let's say I have two projects and need to find the most recent that
>> > > was
>> > > similar to another project. Projects have project numbers that go
>> > > higher
>> > > as
>> > > they become more recent. In the template, I can have the information
>> > > "Calif
>> > > Insert" which represents the client name and project type, and want
>> > > the
>> > > function to go to the database to find all the records that have
>> > > "Calif
>> > > Insert" in a specified column. Then find me from another specified
>> > > field
>> > > the
>> > > largest value in that field that matches "Client Insert" in the other
>> > > field.
>> > > So the answer would be the job number which might look something like
>> > > "5002"
>> > > which represents the most recent job (highest numbered job) that is
>> > > also
>> > > described as "Calif Insert"
>> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list
>> > > that
>> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I
>> > > guess
>> > > VLOOKUP doesn't have the ability to go past the first one it sees.
>> > > Sorting
>> > > the database is not an option.
>> > > I am desparate. Any help would be appreciated.
>> > > Thanks.
>> >
>> >
>> >

I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false. There
should have been an actual job number returned from Column C.

"Alison" wrote:

> I really thought your MAX formula would work, but for some reason it didnt.
> In my scenario, the "Calif Insert" is a changing criteria that I need to be
> able to input in a cell. Then the formula needs to look at that cell and
> then go to the database to find all the records that also contain that same
> description. Then among all the ones it finds that that description in one
> field, return a number to me from another field... the highest number from
> the matches.
> So what I did was on my template (not my database, create a formula that
> refers to the database and to a cell on the template:
> =MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
> Column D is the database column with the description like "Calif Insert" or
> whatever alphabetic description I need to match. In that same column there
> will be other descriptions like "Harris Print Ad" or "First Newsletter" etc.
> B5 is the cell on the template that I will input that same description.
> Column C has the project numbers like 5068 or 5037 etc., and I need the
> largest one of those records that matches the description in column D.
>
> Unless there's a problem with referencing a cell like B5, I'm not sure why
> it didn't work.
> It returned a value of 0. It should have returned an actual job number from
> my test entries.
> "Biff" wrote:
>
> > Hi!
> >
> > >(Instead, I need to look at all the ones that match and return a field from
> > >the largest alphnumberic match).
> >
> > Can you post some examples of these "alpha-numerics" and how do you
> > determine one is larger than another?
> >
> > Do you mean something like:
> >
> > 2001-10-B
> > 2000-75-Z
> >
> > > So the answer would be the job number which might look something like
> > > "5002"
> >
> > If 5002 is a real number:
> >
> > =MAX(IF(A1:A100="Calif Insert", B1:B100))
> >
> > Entered as an array using the key combo of CTRL,SHIFT,ENTER.
> >
> > Biff
> >
> > "Alison" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a template that sends info to a database. In that template, I need
> > >to
> > > put in a function that will use alphabetic information in a cell as my
> > > criteria. It then should look in the database to find records that match
> > > that criteria and then send back to me the largest match from a field of
> > > the
> > > record among the records that match the criteria.
> > > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > > Neither was what I needed. VLOOKUP gives field from the first record of
> > > the
> > > list that matches the criteria. (Instead, I need to look at all the ones
> > > that match and return a field from the largest alphnumberic match). DMAX
> > > for
> > > some reason doesn't work when the database is closed and it doesn't let me
> > > specify criteria for a field.
> > >
> > > Here's an example: the template is used to enter project information for
> > > several clients. Each of those clients may do similar projects. Of those
> > > projects and within each client, several of the projects may be similar.
> > > Let's say I have two projects and need to find the most recent that was
> > > similar to another project. Projects have project numbers that go higher
> > > as
> > > they become more recent. In the template, I can have the information
> > > "Calif
> > > Insert" which represents the client name and project type, and want the
> > > function to go to the database to find all the records that have "Calif
> > > Insert" in a specified column. Then find me from another specified field
> > > the
> > > largest value in that field that matches "Client Insert" in the other
> > > field.
> > > So the answer would be the job number which might look something like
> > > "5002"
> > > which represents the most recent job (highest numbered job) that is also
> > > described as "Calif Insert"
> > > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > > VLOOKUP doesn't have the ability to go past the first one it sees.
> > > Sorting
> > > the database is not an option.
> > > I am desparate. Any help would be appreciated.
> > > Thanks.
> >
> >
> >