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.