I am trying to figure out the excel 2007 database/list/array function which would take a cell containing a list of text values, look them up in a table go to the corresponding row in another column get its value and then return the maximum value for all the items in the list.
A simple example...
Given this database/2 column list:
Name Age
Sally 3
Frank 12
Mark 8
Tim 7
I am looking for something to return the Max age for the items specified in the list so in the table below-for Sally age 3, Frank age 12 returns 12:
List Max Age
Sally, Frank 12
Tim, Mark 8
Sally, Tim, Mark 8
The Name, Age, and List lists are all unsorted.
The Names are always text. The Age and max age are numbers.
List could be an array {Sally Frank} or a comma delimited string:
"Sally,Frank".
Seems like there should be a formulaic way to do this but I haven't figured it out, didn't see it on google, knowledge base, or searching forum. (Part of my problem is I'm not quite sure what the keyword/tag description is of what I am trying to do...)
Any ideas welcome. Thanks!
A simple example...
Given this database/2 column list:
Name Age
Sally 3
Frank 12
Mark 8
Tim 7
I am looking for something to return the Max age for the items specified in the list so in the table below-for Sally age 3, Frank age 12 returns 12:
List Max Age
Sally, Frank 12
Tim, Mark 8
Sally, Tim, Mark 8
The Name, Age, and List lists are all unsorted.
The Names are always text. The Age and max age are numbers.
List could be an array {Sally Frank} or a comma delimited string:
"Sally,Frank".
Seems like there should be a formulaic way to do this but I haven't figured it out, didn't see it on google, knowledge base, or searching forum. (Part of my problem is I'm not quite sure what the keyword/tag description is of what I am trying to do...)
Any ideas welcome. Thanks!
