Excel Excel 2007 home and student

I have a column called "date', "name", "subject". and same person can register many times in diferent days. what I want to do is I need the subject the person take for the last time

edited by moderator: Subject Changed from "can anyone help me"

Let's say you started with this: A B C 1 Date Name Subject 2 1/2/2010 Dave Math 3 1/3/2010 Dave History 4 1/2/2010 Tom Reading 5 1/4/2010 Fred History 6 1/6/2010 Dave ReadingInsert a Column before the Name Column - i.e. a new Column B.

In B2 enter this formula and drag it down:

=C2&COUNTIF($C$2:$C2,C2)

Make sure you use the $ exactly as written.

In B3 you should have:

=C3&COUNTIF($C$2:$C3,C3)

That should give you this:

A B C D 1 Date Name Subject 2 1/2/2010 Dave1 Dave Math 3 1/3/2010 Dave2 Dave History 4 1/2/2010 Tom1 Tom Reading 5 1/4/2010 Fred1 Fred History 6 1/6/2010 Dave3 Dave ReadingYou can hide Column B if you want.

In E1 (or any cell you choose) enter the name of the person you are interested in.

With E1 as an example, use this formula to return the value in the cell next to the last entry for the name in E1:

=VLOOKUP(E1&COUNTIF(C2:C6,E1),B2:D6,3,0)

Using my example data, let's assume Dave is in E1.

=E6&COUNTIF($C$2:$C6,E6) will return Dave3

=VLOOKUP("Dave3",B2:D6,3,0)

will lookup Dave3 in Column B and return the value in the third column of the look_array: "Reading"

I hope that helps.

that was perfect. It works!

Glad to have helped.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History