# Nested IF or LOOKUP Formula needed?

1. ## Nested IF or LOOKUP Formula needed?

I have attached a table I am trying to work from, I need to produce a report for the quarter periods based on the data in the summary sheet (date added / date deleted).

If a vehicle was either added or deleted between X dates (quarter periods) I need it to be copied accross to the appropriate sheet i.e

Line 5 = 1st Quarter (date deleted - 30/01/2009)
Line 6 = Would need to appear in the 2nd & 3rd Quarter (date added - 11/05/2009 (2nd Q) date deleted - 20/07/2009 (3rd Q))
Line 7 = 2nd Quarter (date added - 17/02/2009 / date deleted - 18/02/2009)
Lines 8 & 9 = Not appear anywhere until a date had been inserted against it
Line 10 = 1st Quarter (date added - 01/01/2009 / date deleted - 02/01/2009)
Line 11 = Not appear anywhere until a date had been inserted against it
Line 12 = 2nd Quarter (date deleted - 08/05/2009)
Line 13 = Would need to appear in the 2nd & 3rd Quarter (date added - 14/05/2009 (2nd Q) date deleted - 18/08/2009 (3rd Q))

I have tried using IF statement but not sure if I should be using some form of LOOKUP formula.

Also in column 'G' I have the word 'inception'. Would this effect any formula in any way?

2. ## Re: What type of formula do I need?

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

3. ## Re: What type of formula do I need?

I've added a simple set of quarterly "keys" to your summary in columns J:M. These create a unique sequential index for each quarter and as you've noted, the same row may qualify for multiple quarters.

You can hide/unhide those key columns if you wish by clicking the -/+ sign above column N.

This is a realtime book now, the quarterly sheets will fill themselves out as you do your work on the Summary. The quarterly sheets only have formulas for the first 100 rows.

Have a look. The final formulas bringing the data across to the quarterly sheets is INDEX/MATCH, a very robust way of doing a LOOKUP and does not suffer any of the limitations you find with LOOKUP, VLOOKUP or HLOOKUP. It's my favorite lookup technique.

4. ## Re: Nested IF or LOOKUP Formula needed?

Thanks for that, however when I copy and past my raw data it does not bring across the correct information, for instance there were 60 events in the first quarter.

I have tried to analyse myself however I cannot get a grip on the arrangements, can you take a look at my full list attached.

Also can you recommend anywhere via self learning or courses which will help me with this type of work?

Thanks again for you help

5. ## Re: Nested IF or LOOKUP Formula needed?

When you unhid the key columns, didn't you notice the formulas in J,K,L and M only went down a few rows? You should copy those down as far as you think you'll need rows watched, then hide them again...perhaps 1000 rows or so.

Then, on each Quarter sheet, you copied the formulas down INSIDE the charts so they go down 500 rows, but the "key" formula in column A only goes to row 100, so you'll need to make sure those get copied down the same number of rows on each quarter sheet.

Be careful on the quarter sheets, the formula in A4 should not be copied, only the formula in A5 down.

Lastly, on the summary and the quarterly sheets, try putting your cursor on cell A5 then selecting Window > Freeze Panes. Now scroll down a ways...that should be helpful.

6. ## Re: Nested IF or LOOKUP Formula needed?

I didn't notice there were formula's in those cells, so now yes with the suggestions you made all is working perfectly.

I am looking to book myself in an Excel course for this type of work, can you make any recommendations. I have already done NVQ to level 3 (UK qualification) but that was some years ago and didn't touch on anything like this.

Once again thanks for helping with this, VERY much appreciated.

7. ## Re: Nested IF or LOOKUP Formula needed?

No, I wouldn't have any recommendations, I've learned by stumbling through it all on my own and with the help of forums like this one. Sorry. Maybe if you search the forum for "tutorial" you will find previous answers to that question.

=======
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

##### 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