Questions

Answer for:

formula in excel that can find the last occurrence of each item in a list

Message 2 of 4

View entire thread
+
0 Votes
dogknees

Assuming the first column is in B4:B14, the second column is in C4:C14 and you list of all products in in E4:E10.

In cell F4 enter the following formula. It is an array formula, so you need to enter it, then hold down Shift and Ctrl while you press Enter (Called CSE). If you get it right, the formula will be displayed with curly brackets {}. You can't enter the curly brackets, Excel puts them there when you hit "CSE".

=INDEX($C$4:$C$14,MAX(IF($B$4:$B$14=E4,ROW($B$4:$B$14)-3,-1)))

Copy the formula down to cell F10. If there is no entry, it will show an error, otherwise it will show the last value next each item.