I have a table of info that has 3 columns.
I need to query based upon two values that I choose and then return the highest value of that set.

It's hard to explain but look at the picture of the table and that might help.
This is the formula I am using now and it is only returning the first value - I need the max.

={INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=E2)*($B$2:$B$13=F2),0))}

5/15/2007 10:10:32 PM

1) will column A always be sorted and grouped numerically?
2) will column B be sorted after column A? i.e. can Site 1234 have both Nomal and Query visits, and will they be grouped together within the Site 1234 subset?
3) can you sort by column C as well? i.e. Sort by A, then by C, then by C, therefore garunteeing that each Site and Visit subset will be sorted by date, with the MAX date being the first or last in the subset (in which case your formula would work)

5/15/2007 10:53:40 PM

1) No it will always be random -but always numbers
2) Again - it could be random - it can have both types of visits
3) I am trying everything possible so I don't have sort anything

5/15/2007 11:01:54 PM

damn.... i just wrote a solution based on the assumption 1 and 2 were true.

=MAX(INDIRECT(ADDRESS(MATCH(lookup_visit,INDIRECT(ADDRESS(MATCH(lookup_site,site_array,0) +row_offset,visit_col)&":"&ADDRESS(MATCH(lookup_site,site_array,1)+row_offset,visit_col)),0) +MATCH(lookup_site,site_array,0)-1+row_offset,date_col)&":" &ADDRESS(MATCH(lookup_visit,INDIRECT(ADDRESS(MATCH(lookup_site,site_array,0) +row_offset,visit_col)&":"&ADDRESS(MATCH(lookup_site,site_array,1)+row_offset,visit_col)),1) +MATCH(lookup_site,site_array,0)-1+row_offset,date_col)))

where:
site_array = the column where the sites are listed
lookup_site = the site number to lookup (col E in your table)
lookup_visit = the visit type to lookup (col F)
row_offset = the number of header or blank rows before the site_array table starts (1, in your case)
visit_col = column number of the visit types (B)
date_col = column number where the dates are (C)



use Insert > Name > Define to define those constants and variables so you can leave the variable names in the formula

i'll think about it and maybe look at it tomorrow. The fact that the data in cols A and B can be disjointed (non-grouped, non-sorted) makes it very tough in Excel. might be able to be done in a multistep process, instead of trying to do it all in one magic formula

[Edited on May 15, 2007 at 11:19 PM. Reason : .]

5/15/2007 11:18:15 PM

how will you be generatng the data to lookup - cols E and F?
will you just pick random Sites and Visit types every once in a while to check the Dates? Will you have a whole list? are you trying to make a lookup table of Sites that is always automatically updated with the MAX Date or something?

5/15/2007 11:29:57 PM

Yeah thats the problem - I have the data in columns A-C come from an export and it's pasted into this workbook on a separate sheet.

Then on another worksheet is a whole list of sites and I need to know the MAX date for both Query and Normal visits.

BTW - that is a nasty looking formula and I don't think the named range would work for what I want to do ya know.

5/16/2007 7:38:23 AM

anyone else?

5/16/2007 4:01:24 PM

try askmrexcel.com

i'm thinking something with sumproduct will work but i can't come up with how.

5/16/2007 4:42:20 PM

honestly you're asking for trouble using excel for this purpose. You should use access. Excel is a nice spreadsheet program but for what you want to do Access built in forms would let you do this MUCH easier and simple to convert excel sheets into access databases.

There's a lot of reasons for this but mainly from a query perspective it's far easier to setup in access.

http://support.microsoft.com/kb/286828

Is a good start. I've never created the form myself but my GF works at Duke and converts her excel files to access to run equations and such and builds her forms all the time simply because she said it was too hard to do it in excel. Her setup is similar columnwise

SampleID SampleLocation Date Value1 Value 2 Value 3 etc

5/16/2007 5:09:50 PM

just create a PIVOT TABLE. that will give you all of your 'max' values at once

setup the pivot table so that the sites are in first column, the visit is in the top row and then the MAX of the date is in the main portion of the table, then do lookups from this table

[Edited on May 17, 2007 at 2:24 AM. Reason : .]

[Edited on May 17, 2007 at 2:28 AM. Reason : .]

5/17/2007 2:20:54 AM

^ durrr, good call.
trying to over analyze the problem here.....

5/17/2007 6:43:10 AM

^^ Thanks man
Didn't think about it that way!

5/17/2007 7:09:52 AM