# Identifying First & Last Values in Each Year & Return Adjacent Cell

1. ## Identifying First & Last Values in Each Year & Return Adjacent Cell

Hi all

I have a basic Google Sheet that I am using to track investment performance. I'd love to be able to automatically identify the first entry in each year (whether it be the 1st, 8th, 12th Jan etc) and return the investment value on that date, and then the same with the final entry. I would then be able to look at performance growth year on year.

I have included a link to the data set here:

As you can see (in yellow) on the ROI tab, I have managed to do this using 'MIN' and 'MAX' values, but of course this is no good if the investment value dips after the first entry, or his higher before the final entry. I therefore want to replace the formulas in C5:D13.

2. ## Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

I've not downloaded your document (you can attach it to one of your posts here), but you can apply the MAX and MIN functions conditionally to the date column to find the earliest and latest dates in a particular year (I'm not sure if you have the MAXIF and MINIF functions in Google sheets, but you can simulate these using an array formula where you are looking for the same year). Then you could use the result in a VLOOKUP or INDEX/MATCH formula to extract the appropriate values on those dates.

Hope this helps.

Pete

3. ## Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

Thanks Pete. As it's a Google Sheet it's not so easy to attach (as when I downloaded to Excel format, the Array Formula the current MAX and MIN functions stopped working. You should be able to copy and paste the link into your browser and have edit access.

I'll have another look at it in light of your suggestion and see if I get somewhere!
Appreciate it!

4. ## Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

It seems I've hit the 'magic' 10 posts on here now as well so it has let me post the link. I have updated the original post with it.

5. ## Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

Hi Markrc139,

Not sure if this was what you were looking for or not.
I added a year column to the ISA Value sheet. On the ROI sheet, I used unique, filter, sort and index functions to achieve the results.
Is this something you can work with?

6. ## Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

Thanks Flyboy65 yes that looks spot on. Not quite on the same lines that I'd been thinking before, but a solution that works is a solution that works!

Very grateful for your input.
I'll mark this thread as solved.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1