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

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
i can't enter to my account facebook
susi -
Ambucias -

1 response
If cell = date return yes or no.
JD -
ndwebb -

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

20 responses
WOW equipment return
aakash12345_0667 -
HelpiOS -

1 response