how do i find the last specific value in a column?

[Guest]

i have 2 columns of data. the first is a box number from 1-15 the second is
the time and date the part was weighed. i want to find the last time a box
number was used, say 9, and have the corresponding date copied to another
cell.
thank you in advance for your help, BS.

8 4/25/2006 15:15
11 4/25/2006 15:16
9 4/25/2006 15:16
10 4/25/2006 15:17
15 4/25/2006 15:18
2 4/26/2006 7:15
9 4/26/2006 7:15
7 4/26/2006 7:16
5 4/26/2006 7:24
9 4/26/2006 7:24
14 4/26/2006 7:24
15 4/26/2006 7:26

 

[Bob Umlas]

To find the last 9 in A1:A100:
Ctrl/shift/enter:
=MAX(ROW(1:100)*(A1:A100=9))
Then use this value (say it's in E1) as input to the Index: =INDEX(B:B,E1)
HTH
Bob Umlas
Excel MVP

 

[T. Valko]

Assuming the dates/times in column B are in ascending order (like they are
in your example):

=LOOKUP(2,1/(A2:A13=9),B2:B13)

Format as DATE TIME

Biff

 

[Guest]

This turned out to be the perfect solution for this query can you please
elobarate the formula.
As per the syntax it shoud be:
LOOKUP(lookup_value,lookup_vector,result_vector)

result vector that is B2:B13 (Date & Time) is understandable

lookup_value = 2 ??Why so
Lookup_vector = 1/(A2:A13=9)?? confused.

Please explain??

Shweta Srivastava

 

[T. Valko]

Let's use this example to demonstrate how this works:

...........A.............B
1.....header.....header
2........9.............10
3........7.............12
4........9.............15
5........5.............16

Return the value in column B that corresponds to the *last instance* of 9 in
column A.

=LOOKUP(2,1/(A2:A5=9),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=9)

A2 = 9 = 9 = TRUE
A3 = 7 = 9 = FALSE
A4 = 9 = 9 = TRUE
A5 = 5 = 9 = FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value is
the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/(A2:A5=9)

will not return a value greater than 1.

This is how that would look:

...........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of 9 was in A4. Return the corresponding value from
B4.

=LOOKUP(2,1/(A2:A5=9),B2:B5) = 15

Biff

 

[Guest]

Worked perfectly.
Thanks for the help, I owe you a beer.
BS

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Worked perfectly.
Thanks for the help, I owe you a beer.
BS

 

[Guest]

It is of great help..
Thanks a lot to guide me through such good example.

Shweta Srivastava

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

It is of great help..
Thanks a lot to guide me through such good example.

Shweta Srivastava