+ Reply to Thread
Results 1 to 9 of 9

Using Max values from one sheet and V lookup

Hybrid View

  1. 10-26-2012, 03:04 AM #1
    neeraj_logani
    neeraj_logani is offline
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Using Max values from one sheet and V lookup

    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
    Reply With QuoteRegister To Reply
  2. 10-26-2012, 03:08 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max values from one sheet and V lookup

    Hi

    Probably you can use INDEX-MAX & MATCH functions.

    Upload a small sample workbook, pls.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 10-26-2012, 03:30 AM #3
    neeraj_logani
    neeraj_logani is offline
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Using Max values from one sheet and V lookup

    Enclosed is sample file, sheet one as source and sheet 2 as calculation
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-26-2012, 03:51 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Using Max values from one sheet and V lookup

    Quote Originally Posted by neeraj_logani View Post
    Enclosed is sample file, sheet one as source and sheet 2 as calculation
    Try this in B2 cell of Calculation Sheet
    Formula: copy to clipboard 
    =SUMPRODUCT(MAX(Source!C3:C6*(Source!$B$3:$B$6=$A3)))

    Drag it down and left.

    Hope that helps!


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  5. 10-26-2012, 03:50 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max values from one sheet and V lookup

    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))
    Reply With QuoteRegister To Reply
  6. 10-26-2012, 04:09 AM #6
    neeraj_logani
    neeraj_logani is offline
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Using Max values from one sheet and V lookup

    No luck it return to Value nothing happens
    Reply With QuoteRegister To Reply
  7. 10-26-2012, 04:11 AM #7
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Using Max values from one sheet and V lookup

    Quote Originally Posted by neeraj_logani View Post
    No luck it return to Value nothing happens
    Sorry... Try it in B3 cell of calculation sheet

    Formula: copy to clipboard 
    =SUMPRODUCT(MAX(Source!C3:C6*(Source!$B$3:$B$6=$A3)))
    Reply With QuoteRegister To Reply
  8. 10-26-2012, 04:18 AM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max values from one sheet and V lookup

    sEE THE example...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-26-2012, 05:25 AM #9
    neeraj_logani
    neeraj_logani is offline
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Using Max values from one sheet and V lookup

    Thank you Both, it helped me solve my problem
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1