Function to look up largest value whose record meets criteria?

[Alison]

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/communitie...n-us&mid=14871180-c699-40be-ae67-0284e231fa7e

 

[Bob Phillips]

Alison,

Try something like

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

--

HTH

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

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/communitie...ia+in+database&dg=&cat=en_US_d02fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

 

[Bob Phillips]

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)

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/communitie...ia+in+database&dg=&cat=en_US_d02fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

 

[Alison]

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]

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)

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?

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)



http://www.microsoft.com/communitie...ia+in+database&dg=&cat=en_US_d02fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e

 

[Alison]

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

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)

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?

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)


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/communitie...ia+in+database&dg=&cat=en_US_d02fc761-3f6b-40
2c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-u
s&mid=14871180-c699-40be-ae67-0284e231fa7e