Previous Topic Next Topic

Find last time text entered and return value

[Solved/Closed]
Report
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
 -
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
 -
Hello,

I've got text in column D and a value for it in column G. I want to lookup/find the last time "Bolt 'was entered and return it's value.(the list in column D can have up to 300 rows of text in with values in G.

Column A Column D Column G
06/05/2010 07:33:53 Nuts 10
06/05/2010 11:53:43 Bolts 12
07/05/2010 14:20:30 Screws 15
08/05/2010 08:10:23 Bolts 13

So the bolts last entered value was 13 that must be returned.



2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Try this Gouws

=SUMPRODUCT((G1:G10) * (D1:D10="Bolts") * (A1:A10=SUMPRODUCT(MAX((D1:D10="Bolts") * (A1:A10) )* 1)) *1 )
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
rivisa i've used =LOOKUP(2,1/($D$2:$D$9=H2),$G$2;$G$9) and put the name Bolts in H2. The only problem is when a negative number is entered.
I'm looking it up from the bottom to the top!
Answer 2 / 2
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
Hi rivisa1

It give me : #VALUE back
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
I used your sample on row 1:3

and used this formula for Bolts
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Bolts") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Bolts") * (A$1:A$10) )* 1)) *1 )

I got 13


use this formula for Screws
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Screws") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Screws") * (A$1:A$10) )* 1)) *1 )
and got 15


use this formula for nuts
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Nuts") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Nuts") * (A$1:A$10) )* 1)) *1 )
and got 10


May be if you post a link to you file that would help
Report
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
TX, rivisa 1 found the problem it's working 100%
Ask a question