Find the last entry in a list

HappsLucks

Board Regular
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>

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Comfy

Well-known Member
Here's one way:

Excel 2010
ABCD
1ywhite
2yblue
3ygreen
4*orange
5yyellow
6yred
7yblack
8*violet
9ymagenta
10*purple
11ypinkpink
Sheet1
Cell Formulas
RangeFormula
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

Well-known Member
Try

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

Enter as an array - Ctrl, Shift & Enter

Last edited:

Weazel

Well-known Member
maybe...

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

HappsLucks

Board Regular
Here's one way:

Excel 2010
ABCD
1ywhite
2yblue
3ygreen
4*orange
5yyellow
6yred
7yblack
8*violet
9ymagenta
10*purple
11ypinkpink

</tbody>
Sheet1

Array Formulas
CellFormula
D11{=INDEX(B1:B11,SMALL(IF(A1:A11="y",ROW(A1:A11)-ROW(INDEX(A1:A11,1,1))+1),COUNTIF(A1:A11,"y")))}

</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.

Replies
3
Views
621
Replies
6
Views
1K
Replies
2
Views
942
Replies
1
Views
502
Replies
2
Views
497

1,191,707
Messages
5,988,230
Members
440,139
Latest member
ngaicuong2017

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back