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.



Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 )
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!
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
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%

Similar discussions

If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
WOW equipment return
aakash12345_0667 -
HelpiOS -

1 response
i can't enter to my account facebook
susi -
Ambucias -

1 response
The system time is invalid
LeonardPromise -
Anonymous User -

5 responses
Facebook says that my email doesn't match my facebook email
@@@@@@@@@@@ -
Ambucias -

1 response