Find the last entry in a list

[HappsLucks]

Hi, hope somebody can offer a simple solution, I have gone blank today.

Columns A and B are populated be a series of intricate formula.
I need a formula to return a value from Column B.

From the example below, the formula should return the value for the last "y" in Column A from B - in this case, "Pink".

<title>Excel Jeanie HTML</title>******>Sheet1 * A B 1 y white 2 y blue 3 y green 4 * orange 5 y yellow 6 y red 7 y black 8 * violet 9 y magenta 10 * purple 11 y pink <tbody> </tbody> Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

<tbody>
</tbody>

 

[Comfy]

Here's one way:

Excel 2010
	A	B	C	D
1	y	white
2	y	blue
3	y	green
4	*	orange
5	y	yellow
6	y	red
7	y	black
8	*	violet
9	y	magenta
10	*	purple
11	y	pink		pink
Sheet1

Cell Formulas
Range	Formula
D11	{=INDEX(B1:B11,SMALL(IF(A1:A11="y",ROW(A1:A11)-ROW(INDEX(A1:A11,1,1))+1),COUNTIF(A1:A11,"y")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gaz_chops]

Try

=INDEX(B1:B11,MATCH(2,1/(A1:A11="y")),0)

Enter as an array - Ctrl, Shift & Enter

 

[Weazel]

maybe...

=LOOKUP(2,1/(A1:A11="Y"),B1:B11)

 

[HappsLucks]

Here's one way:

Excel 2010

	A	B	C	D
1	y	white
2	y	blue
3	y	green
4	*	orange
5	y	yellow
6	y	red
7	y	black
8	*	violet
9	y	magenta
10	*	purple
11	y	pink		pink

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D11 {=INDEX(B1:B11,SMALL(IF(A1:A11="y",ROW(A1:A11)-ROW(INDEX(A1:A11,1,1))+1),COUNTIF(A1:A11,"y")))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you, works a charm.