Using Max values from one sheet and V lookup

[neeraj_logani]

Hi All,

I have to create a report and i am working on sheets, Sheet 1 contains data column 1 has Name and column 2 has amount spent by individual. the condition is that i would like to create a report on sheet 2 with name of a person and maximum amount spent by that person.

So if sheet 1 column has Joe in column A and in column B we have 200 Rs as entry then if there is another entry for joe in column B and amount is more than 200 i.e. 225 or 250 then sheet 2 column a with joe as a name should reflect on 225 or 250.

We can do this using Pivot however i will run these on Ubuntu systems too so just making sure i this can be used there as well

[Fotis1991]

Hi

Probably you can use INDEX-MAX & MATCH functions.

Upload a small sample workbook, pls.

[neeraj_logani]

Enclosed is sample file, sheet one as source and sheet 2 as calculation

[Fotis1991]

In B3, put this ARRAY(Confirm using Control+Shift+Enter. Not just Enter) formula. Copy down and to the right.

=MAX(IF(Source!$B$3:$B$6=Calculation!$A3,Source!C$3:C$6))

[:) Sixthsense :)]

Enclosed is sample file, sheet one as source and sheet 2 as calculation

Try this in B2 cell of Calculation Sheet

Please Login or Register  to view this content.

Drag it down and left.

Hope that helps!

[neeraj_logani]

No luck it return to Value nothing happens

[:) Sixthsense :)]

No luck it return to Value nothing happens

Sorry... Try it in B3 cell of calculation sheet

Please Login or Register  to view this content.

[Fotis1991]

sEE THE example...

[neeraj_logani]

Thank you Both, it helped me solve my problem