Op vrijdag 4 mei 2007 14:46:41 UTC-4 schreef 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

"Shweta Srivastava77" wrote
in message ...
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" wrote:

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

"bradsmith37" wrote in message
...
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





Is there a way to make this formula work if you are dealing with text in column A versus numbers??