+ Reply to Thread
Results 1 to 5 of 5

Find the last value in a range with condition

  1. #1
    Registered User
    Join Date
    04-23-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Find the last value in a range with condition

    Hi all first post so i hope i am not posting something very common. i have been looking through the net but was unable to find something like this

    basically i have a set like this

    ----A----------B-------C
    INTEL-----2000
    INTEL-----4000
    BP-------10000
    INTEL-----8000
    BP-------30000
    GOOGLE --2000
    INTEL---- 4000

    Each cell a is a stock with b being the cumulative units accumulated. I am looking for a formula that returns to me the last cell B if A is INTEL for example and i will input this formula into cell c.

    this is to find the previous no.

    i was able to do this with a google spreadsheet formula as follows

    INDEX(arrayformula(filter($B1:$B$2,$A1:$A$2<>"",row($A1:$A$2)=max(if($A1:$A$2="INTEL",row($A1:$A$2),0)))) ;1)

    but i dunno how to do it with excel. hope i can find some understanding here.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Find the last value in a range with condition

    Maybe...

    =LOOKUP(2,1/(A1:A7="INTEL"),B1:B7)
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    04-23-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find the last value in a range with condition

    thanks for the replly. i am still seeing if it works. but what does 1/(A1:A7="INTEL") do? why 1/?

  4. #4
    Registered User
    Join Date
    04-23-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find the last value in a range with condition

    ah i sort of found the answer myself

    It's making very hard work of looking up a value (2) it wont find in the
    range so it will return the last instance of the maximum value.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Find the last value in a range with condition

    Take a look at this

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1