in cell forumla

[MINCUS1308]

Without using VBA, is there a way to do the following?
Given the following table I would like to determine the largest number for any individual given the 'Type #'.



Name
Type 1
Type 2


Taylor
1
9


Josh
2
8


Kyle
3
7


Kyle
4
6


Bob
5
5


Josh
6
4


Taylor
7
3


Taylor
8
2


Bob
9
1



So if I looked for Taylor, 'Type 1' the cell would return: 8
Josh: 6
Bob: 9
Kyle: 4
And if I looked for Taylor, 'Type 2' the cell would return: 9
Josh: 8
Bob: 5
Kyle: 7

The general idea is to have a table populated by the most recent entry in a much larger table.

[Yongle]

Yes - standard functionality if you create an Excel Table

Put your cursor anywhere in your range
tab "Insert" , click on "Table"
tick the box "Total Row"

Can then select any "name" in dropdown at top of table and if you go to the Total Row select Max (or Min or Avg or ....etc)

Worksheet with table with your data included attached to this post

[MINCUS1308]

This is a solution but it wont be applicable in my situation... Ill just code it :p

[Yongle]

I think you have thrown the towel in much too early here.

Look at attached (amended) workbook and amend A15 using the dropdown

The formula you are looking for in a cell is an array . To enter an array formula remember to hit CTRL SHIFT ENTER otherwise no curly brackets and you cannot input them!! I keep swearing - on autopilot and always hit ENTER grrr!

{=MAX(IF(Name=A15,Type_1))}

Name = column A of the table
Type_1 = column B of the table
Column K contains my named range = UniqueNames used in the dropdown in A15