excel - Return max index value in vlookup

Question

I'm trying to pair a vlookup with a max function. For some reason it only returns #ref every time I try to use it though.

My sheet looks like this:

      A  -  B  -  C  -  D  -  E  -  F  -  G
1...
5 - Prod5  id1  $100  $125  $155  $110  $150
6...

A:G is named buyAverages C:G is named buyAveragesPrices

What I want to do is have a vlookup go and find a value in Col A and then return the highest value in that Col. So example:

      A  -  B
1 - Prod5 *return highest price for Prod5

What I wrote in B1, which failed:

VLOOKUP(A1,buyAverages,MAX(buyAveragesPrices))

So how do I achieve this lookup? Everything I have found is how to use MAX for the lookup value, but nothing to use max on the returned index.

Answer 1

Try this

=MAX(IF(A:A="Prod1",C:G))

This is an Array Formula. i.e you have to press Ctrl+Shift+Enter