Array Formula

[Cra5h]

{=IFERROR(INDEX('C:\Shared Documents\Mandatory Training\[Email Data 2014.xls]Sheet 1'!$C$1:$L$60000,(MATCH(C5&$E$2,'C:\Shared Documents\Mandatory Training\[Email Data 2014.xls]Sheet 1'!$C$1:$C$60000&'C:\Shared Documents\Mandatory Training\[Email Data 2014.xls]Sheet1'!$E$1:$E$60000,0)),4),$O$2)}

The above formula is entered as an array formula and does the following.
Having looked at my data file & matched the person (cell C5) with the course name ($E$2) the formula returns the value in 4th column or if it can’t match the person/course name it returns the value that I place in cell $O$2 e.g. “not registered”

What I want to know is whether or not there is an easier and less demanding (on excel) formula. This is because I am looking to set this up to cover probably 2000 people (so 2,000 rows & about 6 columns to cover the different courses) and the data file could cover some 50,000 rows of data and it seems to take excel an awful long time to ‘update links’ successfully and also once I've input the formula in 1 row as an array formula - for me to drag the formula down just 500 rows seems to take absolutely ages or even freezes excel.

Could anyone help please?

Dave

[XOR LX]

Hi,

Performing the concatenations in helper columns and referencing those ranges instead should improve things considerably.

As I imagine would having the source file open during re-calculation.

Regards

[Cra5h]

I did try having the source file open but still seemed to take ages. Can you explain how i could use the concatentate formula to help in this situation??

Thanks

Dave

[XOR LX]

You're already concatenating!

I mean, instead of performing the concatenation (the ampersands) of the two source ranges within the formula, so that, effectively, each formula has to perform this concatenation as part of its calculation, why don't you add an extra column to your source data and use this to concatenate columns C and D?

You then simply reference this one column in the formula instead.

You can also do a similar exercise for the match_values, for which you are also performing the concatenation within the formula itself, and therefore adding extra resource into the calculation.

Also consider whether 60,000 is an absolute minimum upper limit for your range references. Array formulas will calculate over all cells in the specified range, whether empty or not.

Regards

[XOR LX]

In fact, I forgot the most important point!!

If you don't need to concatenate within the formula, then this formula doesn't need to be entered as an array formula!

Regards

[Cra5h]

Sorry but I have only 'some' knowledge of excel and don't really understand what exactly you mean. I have uploaded a sample which hopefully shows better what I need to accomplish. Also not sure whether I need to 'Concatenate' or not but basically I need a formula that will look to match 2 pre-defined items (Employee No and course name) within my data and give an answer on the same row where it finds this information.

sorry if that isn't very well explained but hopefully my upload will be a little clearer ?

Thanks again

Dave

[XOR LX]

I'll try once more.

Add an extra column to your DATA sheet. Let's use column K for now since it is empty.

Put this formula in cell K2:

=D2&E2

Copy down as required.

Your formula in D2 of the FINAL SHEET is now:

=IFERROR(INDEX(DATA!$D$1:$J$6000,MATCH($B2&$D$1,DATA!$K$1:$K$6000,0),3),$O$2)

and no longer needs to be entered as an array formula.

Regards

[Cra5h]

Thanks that seems to work so i will try it on my data sheet and hopefully that will make it much quicker. Thanks for the help but don't think there was a need for "I'll try once more". We're not all familiar with many excel formula's hence the forums...

[XOR LX]

You're welcome. And you're right - there was no need for it, so apologies.

Regards