Find Last Occurrence of Number

[KimC]

Hi, I'm trying to work out how if I have the data (as seen below in "Sheet
3"), how to use that to populate "Sheet 2" as shown.

I'm wanting to find the "name" in sheet 3 column B that is the last
occurrence of that value in column A that corresponds with the value in sheet
2 column A as long as sheet 3 column C is blank.

Sheet 3
A B C
1 5 Name 1 12/01/2009
2 1 Name 2 10/01/2009
3 5 Name 3
4 4 Name 4
5 2 Name 5

Sheet 2
A B C
1 1
2 2 Name 5
3 3
4 4 Name 4
5 5 Name 3

Thanks in advance
Kim

 

[KimC]

I also forgot to mention that the columns in sheet 3 will continually have
data added to them.

Any help with this would be great thanks.

 

[Joel]

This is a little bit complicated. You need an IF so you don't get N/A
results when you don't return anything. The formula is in the lookup
function is in the equation twice to aviod the N/A.

The lookup array return something like this

=Lookup(2,1/{1,0,0,1,1,0,1},C1:C7)

=Lookup(2,{1,DIV 0,DIV 0,1,1,DIV 0,1},C1:C7)

The number two will never match anything exactly in the array. So instead
it returns the last 1 in the array which is the number smaller than 2. The
DIV 0 is a very large number.


=IF(ISNA(LOOKUP(2,1/(--(Sheet3!$A$1:$A$100=A1)*--(Sheet3!$C$1:$C$100="")),Sheet3!$B$1:$B$100)),"",LOOKUP(2,1/(--(Sheet3!$A$1:$A$100=A1)*--(Sheet3!$C$1:$C$100="")),Sheet3!$B$1:$B$100))

 

[T. Valko]

Try it like this:

=IF(ISNA(LOOKUP(2,1/((Sheet3!A$1:A$5=A1)*(Sheet3!C$1:C$5="")))),"",LOOKUP(2,1/((Sheet3!A$1:A$5=A1)*(Sheet3!C$1:C$5="")),Sheet3!B$1:B$5))

 

[KimC]

Thankyou to both of you - helped heaps

Try it like this:

=IF(ISNA(LOOKUP(2,1/((Sheet3!A$1:A$5=A1)*(Sheet3!C$1:C$5="")))),"",LOOKUP(2,1/((Sheet3!A$1:A$5=A1)*(Sheet3!C$1:C$5="")),Sheet3!B$1:B$5))