Search code examples
excelif-statementexcel-formulaworksheet-functionarray-formulas

Find the maximum value associated with a specific value in a different column

I need to find the largest value in column B associated with a specific value in column A. Here's an example of what I mean:

A      B

2       1
2      14
2      22
5       6
5       8
5      99

I.e. I want to find the the largest value, 22, in column B on the same row as a 2 in column A.

I'm currently using this to try find the value, however it only finds the first value:

 =VLOOKUP(A2, A1:B20, 2, FALSE)
Solution

  • Please try: 

    =MAX(IF(A:A=2,B:B))

    entered with Ctrl+Shift+Enter.