Previous Topic Next Topic

Find last time text entered and return value

Solved/Closed
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
 - Aug 5, 2010 at 02:29 AM
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
 - Aug 10, 2010 at 08:01 AM
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
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Aug 5, 2010 at 07:22 AM
Try this Gouws

=SUMPRODUCT((G1:G10) * (D1:D10="Bolts") * (A1:A10=SUMPRODUCT(MAX((D1:D10="Bolts") * (A1:A10) )* 1)) *1 )
1
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
Aug 6, 2010 at 02:03 AM
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!
0
Answer 2 / 2
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
Aug 6, 2010 at 12:17 AM
Hi rivisa1

It give me : #VALUE back
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Aug 6, 2010 at 10:55 AM
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
0
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
Aug 10, 2010 at 08:01 AM
TX, rivisa 1 found the problem it's working 100%
0