# Find the last value in a range with condition

1. ## 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
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. ## Re: Find the last value in a range with condition

Maybe...

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

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. ## 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. ## Re: Find the last value in a range with condition

Take a look at this

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

#### 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