VLOOKUP, wanting 2nd, 3rd occurrence, etc.

**nj8988** · 07-03-2012, 11:29 AM

Hello, if anyone can help me with this I would much appreciate it.

In Cell A9:25, I want the date (Column E on .25! Sheet) of any name match (Column C on .25! Sheet). There will be eventually numerous entries for each name, so I would like it to return the 1st, 2nd, 3rd, 4th, etc. date. I know how to get the first one using VLOOKUP, but I am stuck after that. Attached is the spreadsheet.

Again thanks to anyone who can help me.

- Nick J.

**Spencer101** · 07-03-2012, 11:51 AM

Here's one way. It uses two helper columns (A on the "Occurrence Sheet" and D on the ".25" one).

On the .25 sheet it simply counts the number of times the name appears in the current row and any rows above it and adds that number to the beginning of the name.

On the Occurrence Sheet the numbers in column A are added to the name in cell C3, but it's done within the formula so you never see it.

You can hide the two helper columns and be none the wiser.

Let me know if this is of use or even what you meant....

S.

**tigeravatar** · 07-03-2012, 11:58 AM

nj8988,

In sheet 'Occurrence Sheet' cell A9 and copied down:

Please Login or Register  to view this content.

**nj8988** · 07-03-2012, 02:28 PM

Thank you very much. This worked! I have one more issue to work around. On the sheet "Rollover!", I am looking to find the MAX Value (date) in column E in the .25! if column A on Sheet Rollover! matches Column C on Sheet .25!

(On the sheet attached).

**Spencer101** · 07-03-2012, 02:49 PM

In cell B2 on the Rollover worksheet, enter the below formula. It is an "array formula" so you have to enter it using Ctrl+Shft+Enter rather than just Enter.

=MAX(IF('.25'!C$1:C$41=A2,'.25'!E$1:E$41))

Then copy down and format the cells to a date style.

You will get only 4 actual dates in your example because there are only 4 individual names on the .25 worksheet.