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

Answer 2

If there's only one instance of each Product then you can use INDEX/MATCH like this

=MAX(INDEX(C2:G100,MATCH("Prod 1",A2:A100,0),0))

Longer than Sid's suggestion but doesn't need CSE and might be more efficient if you only have a single match

If you have that formula in Z2, for example, you can use this version to get the location from row 1

=INDEX(C1:G1,MATCH(Z2,INDEX(C2:G100,MATCH("Prod 1",A2:A100,0),0),0))

Answer 3

You can have all in one cell using Vlookup and Max with a nested formula. For example at the top of the page:

A1 = Select the name of the product you want to find the max

A2= MAX(BUSCARV($A$1;$A$3:$F$11;3;FALSO);BUSCARV($A$1;$A$3:$F$11;4;FALSO)
;BUSCARV($A$1;$A$3:$F$11;5;FALSO);BUSCARV($A$1;$A$3:$F$11;6;FALSO))

It's long but you only have to type it once. With this formula we get all the different amounts in each column and then we ask for the maximum. It works if all the products are different. Change the name of the product and you'll find the MAX in the table.

Example Prod8

Prod8 41 ; If we change and you select in A1 Prod4 you'll get 70 and so on..

Prod1 id1 100 125 155 110

Prod2 50 25 20 75

Prod3 60 65 15 90

Prod4 70 12 50 43

Prod5 100 200 80 25

Prod6 20 28 40 40

Prod7 14 43 60 80

Prod8 22 33 15 41

Prod9 65 48 50 70

Select your range accordingly.

You also could include in A1 a match code to select the name of your products..