How to nest multiple iferror statements with multiple index matches?

Hi all,

Wondering if someone can help me.

I'm trying to combine multiple iferror statements with multiple index matches and I'm not getting the results I'm after. I've attached an example spreadsheet which shows the formula I'm currently trying. The results on line 3 in the 1st tab in green are the correct results I am expecting, but the results in red underneath are what I'm hoping to see...but am not.

[Context to this - we go to multiple sites which are recorded by "site number" and record how many minutes were spent at each meeting. (There is a formula in the "raw data" tab in column D + E which sort out the order of the dates, creates a reference, which is then used in the formula in the "report" tab to order the data).

However, a site number could change, yet we still need the data for the old site number to sit alongside the new site number (hopefully the example explains this a bit better).

This could happen across multiple sites, but for simplicity I've just added in data for 1 site which has changed its site number. ]







I don't know if its just a simple fix in the way I'm trying to do the formula (I have tried googling this already but with no luck) or my approach is just wrong.

Any help / advise would be great.


Thanks !

Hi Special-K

Thanks for taking a look into this (and sorry for the delay in replying).

I tried out the formula but it only gives me the results that I have in green (wihin the attached example). In the first formula, I'm not sure how I reference both the old and new site numbers, as I believe that the formula is only looking at column B? ... but then, I have never used the LOOKUP function before so I could well be wrong !

Hi,

so I would need a formula that can be applied from columns D - K and look in column B first and bring back dates, then look in Column A and bring back dates. Reason being, that it may not always be in column H where dates need to brought back for a new site number, it could be in any of them e.g. an old site only has one date (column D) and then from column E onwards dates should be brought back relating to the new site if that makes sense?

Essentially, a site could change number at any time in the year. Also, visits aren't always monthly, they could be quarterly, every 2 months etc (so there's no set pattern to follow).
As such, the same logic applies to columns M-T - that it needs to be able to read the site numbers in both column B and A.


So the ammended formula does now work in bringing back the dates in red, but is there a way to combine this with your initial formula, so that across the whole section, the formula looks at both the old and new sites?

Thanks again for your help though !!

So I just tried this and it still only picks up the dates for the old site. I guess because there is data in B3 (i.e. it's not blank), the IF statement doesn't work for what I need it to do because it doesn't know to move onto A3 when there are no more dates left for B3, yet there are dates for A3.

The more I think about this the more I really don't know if this is possible

Sorry, that's my fault the dates in red should have been 01/05/20199,01/06/2019,01/07/2019 but for some reason I missed that I'd put those dates in like that.

The thing is, there could be 20 sites where their numbers have changed and they could have changed after the 1st meeting, the 2nd or the 3rd etc, so there wouldn't be a way of knowing for each site (of which there are almost a 1000 in real life) at what stage they changed and therefore, which column relates to the old vs new site number for all sites i.e, there's no hard and fast rule to say Column D will always = the old site and E onwards will always = the new site. So column G for example may relate to the old site number for some sites and for others, who have never had a site number change, it would only relate to the current site numbers.

Hence the need for something dynnamic so that, each month when I put the raw data into the "Raw data" tab it quickly populates data into the report (for context, I would know from a seperate summary report I receive when sites have changed numbers and what the new site number is, and I manually update the site numbers ready for when we get the raw data)

Hi JeteMc,

thanks so much for taking the time to look at this. I just tried out the formula and it works perfectly !!

With that first formula, how on earth do you even begin to come up with something like that? I can see what is happening in the 'time' formula with the index matches and if errors, but never in a million years would I have been able to come up with the first one.

I'm trying to get my head around what the 1st formula is actually doing. I tried to google the Aggregate function but can't quite understand what it's actually doing in this formula. And the Columns function in the end where it's just looking at row A, what does that but do?

Thanks again!

Thanks so much for taking the time to explain this, that all makes perfect sense. I feel like every day I learn something new about the capabilities of Excel.

Thank you again for all your help