Function to look up largest value whose record meets criteria?

A

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
 
B

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)


Alison said:
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
 
B

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)


Alison said:
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
 
A

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?
 
B

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)


Alison said:
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 said:
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
 
A

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



Bob Phillips said:
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 said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top