Find Last Occurrence in List

[deepheat101]

Peeps...

I have the following data...

       A     B
100  01-Jan  0
101  02-Jan  0
102  03-Jan  1
103  04-Jan  0
104  05-Jan  0
105  06-Jan  2
106  07-Jan  0
107  08-Jan  0
108  09-Jan  1
109  10-Jan  0

Considering that the list will be added to each day, how do I go about finding date of the last occurrence of any given number in column B. For example the last 0 would return "10-Jan"; the last 1 would return "09-Jan"; the last 2 would "06-Jan". I had been working with VLookUp before quickly realizing that I need a VLookDown...

Cheers

 

[Oaktree]

Try:

=LOOKUP(2,1/(B1:B10=1),A1:A10) for the last instance of 1 or

=LOOKUP(2,1/(B1:B10=0),A1:A10) for the last instance of 0

 

[Jonmo1]

Try this

<TABLE style="WIDTH: 565pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=753 border=0 x:str><COLGROUP><COL style="WIDTH: 565pt; mso-width-source: userset; mso-width-alt: 27538" width=753><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 565pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=753 height=17>=LOOKUP(2,1/(B100:B109=1),A100:A109)</TD></TR></TBODY></TABLE>

Bold Red 1 is the part to adjust for either 0 1 or 2..
You can change that to a cell reference that contains a 0 1 or 2..

Hope that helps..

 

[deepheat101]

Many Thanks for your very speedy (and very correct) responses..

Much Kudos!

 

[DonkeyOte]

Oaktree / jonmo...

is there a good resource for learning the extended *possibilities* of LOOKUP ?

I've seen a couple today and am intrigued as to how they work... Aladin provided the other.

I've read through the standard XL help which taught me the basics -- was hoping you may know of a good resource site to look at ??

 

[Jonmo1]

You're looking at it. MrExcel.Com.

Everything I have learned (beyond your basic Vlookup and using the macro recorder) I learned here.

 

[Quasi]

This array formula should work nice too

=INDEX($B$1:$B$10,MAX(IF($C$1:$C$10=E1,ROW($C$1:$C$10),"")))

 

[jellybeank28]

Quasi

Any idea what formula I can use for the following?

Quoted Part	Quote #	RFQ Date	Sales Order Part	Job/SO	Order Date	Last Date purchased
202657 - PIN ASSEMBLY	4448	4/14/04	497474-3 CUST NO. MRX00003517	3928-001	4/16/04	#VALUE!
	4487	6/10/04	MC114N CUST NO. MRX00003783	3928-003	4/16/04	04/16/04
801963 REV A	4671	4/7/05	ER412-12A CUST NO MRX00031771	3928-006	4/16/04	#N/A
801964 REV A	4671	4/7/05	030-2409-001 CUST P/N 01308805	3927	4/20/04	#N/A
204795 REV -	4671	4/7/05	7889-1505	3929	4/21/04	#N/A
204562 REV C	4671	4/7/05	801870D	3926	4/22/04	#N/A
25M0224	4828	3/1/06	9609-0090	3932	4/28/04	#N/A
9645-0097 REV A	4537	3/13/06	9643-0090	3933	4/28/04	04/28/04

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In the very last column I have the following formula:
=INDEX($A$2,MAX(IF($N$2:$N$7000<D2,ROW($J$2:$J$7000),"")))
But as you can see in the 2nd row its feedback 4/16/04 instead of a date that is greater than 6/10/04 (3rd column). All I want is for the spreadsheet to look at the "Quoted Part" and find the most recent date (later than the "RFQ Date" that it was ordered based on "Sales Order Part" and "Order Date" columns. In the end I only need to see the last date it was ordered in the very last column.

Any help would be much appreciated!
Kelly