Find Last Occurrence of Number
 

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

Find Last Occurrence of Number
 

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.

"KimC" wrote:

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

Find Last Occurrence of Number
 

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)),"",L OOKUP(2,1/(--(Sheet3!$A$1:$A$100=A1)*--(Sheet3!$C$1:$C$100="")),Sheet3!$B$1:$B$100))

"KimC" wrote:

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

Find Last Occurrence of Number
 

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Joel" wrote in message
...
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)),"",L OOKUP(2,1/(--(Sheet3!$A$1:$A$100=A1)*--(Sheet3!$C$1:$C$100="")),Sheet3!$B$1:$B$100))

"KimC" wrote:

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

Find Last Occurrence of Number
 

Thankyou to both of you - helped heaps :)

"T. Valko" wrote:

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Joel" wrote in message
...
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)),"",L OOKUP(2,1/(--(Sheet3!$A$1:$A$100=A1)*--(Sheet3!$C$1:$C$100="")),Sheet3!$B$1:$B$100))

"KimC" wrote:

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