I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:

http://www.microsoft.com/communities...7-0284e231fa7e

Alison,

Try something like

=MAX(IF(B1:B100="the_value",C1:C100))

--

HTH

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


"Alison" wrote in message
...
I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my
case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:


http://www.microsoft.com/communities...2fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptli st=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

Should have mentioned that that is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

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


"Alison" wrote in message
...
I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my
case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:


http://www.microsoft.com/communities...2fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptli st=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

Hello there. This is the actual based on your model:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B19,'[Database.xls]Sheet1'!$C:$C))
Where database sheet 1 column 2, obviously, is what I want to search...
B19 is the cell on my template that has the formula "=B4&B5&B6 which creates
a string of text that becomes the value I want to look for in the database
and column C is the one I want the answer from, the largest of the matches.

Unfortunately, it didn't work. I got a #NUM!

Is it because B19, "the value" as you put it, is not numeric?


"Bob Phillips" wrote:

Should have mentioned that that is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

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


"Alison" wrote in message
...
I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my
case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:


http://www.microsoft.com/communities...2fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptli st=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

Hi Alison,

No, B19 being text doesn't matter as long as column B is also text.

The problem is complete columns, reduce it down

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

and don't forget the Ctrl-Shift-Enter


--

HTH

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


"Alison" wrote in message
...
Hello there. This is the actual based on your model:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B19,'[Database.xls]Sheet1'!$C:$C))
Where database sheet 1 column 2, obviously, is what I want to search...
B19 is the cell on my template that has the formula "=B4&B5&B6 which
creates
a string of text that becomes the value I want to look for in the database
and column C is the one I want the answer from, the largest of the
matches.

Unfortunately, it didn't work. I got a #NUM!

Is it because B19, "the value" as you put it, is not numeric?


"Bob Phillips" wrote:

Should have mentioned that that is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

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


"Alison" wrote in message
...
I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my
case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:



http://www.microsoft.com/communities...2fc761-3f6b-40

2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptli st=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

Bless your heart, Bob; you are a perfect angel!
After a couple of quick tests, it looks like it is working. Thank you so
much. I'll continue to test this week and post again if I have any updates.
Thank you!

Alison



"Bob Phillips" wrote:

Hi Alison,

No, B19 being text doesn't matter as long as column B is also text.

The problem is complete columns, reduce it down

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

and don't forget the Ctrl-Shift-Enter


--

HTH

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


"Alison" wrote in message
...
Hello there. This is the actual based on your model:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B19,'[Database.xls]Sheet1'!$C:$C))
Where database sheet 1 column 2, obviously, is what I want to search...
B19 is the cell on my template that has the formula "=B4&B5&B6 which
creates
a string of text that becomes the value I want to look for in the database
and column C is the one I want the answer from, the largest of the
matches.

Unfortunately, it didn't work. I got a #NUM!

Is it because B19, "the value" as you put it, is not numeric?


"Bob Phillips" wrote:

Should have mentioned that that is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

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


"Alison" wrote in message
...
I am wracking my brain here.
I need to formulate a function
that will search a database for several records that match alphabetic
criteria
created from a formula on the template,
then look at another field containing numeric info in those records
and return back to me the largest of those numeric values.

No such luck.

VLOOKUP returns the first one it sees (sorting is not an option in my
case).
MAX doesn't look at criteria.
IF hasn't worked the way I had it.
DMAX doesn't work and you have to have the database open.

PLEASE HELP!!!!
I've already tried what's at the below post:



http://www.microsoft.com/communities...2fc761-3f6b-40

2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptli st=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e