Should I avoid these at all costs or is it ok to use some VBA code or array formulas (better for the examiner that is? What about vlookup/hlookup - especially with the row-index and col-index parameter - is it better to avoid (since it is difficult to modify your spreadsheet - inserting a column breaks it- so it is better for yourself to avoid it)? Would it be deemed too complex too circumvent using something like =VLOOKUP(B4,B3:C6,COLUMNS(B3:C6)) instead of =VLOOKUP(B4,B3:C6,2)?
Use of VBA, array formulae etc When I have attended (more than once), we were told very clearly not to use VBA. I think HLOOKUP & VLOOKUP are fine - the specimen assignments on the Institute's website use these functions. As for array formulae, that may depend on the context. I find they are useful at work, particularly when you know the spreadsheet layout won't change much, if at all. For CA2, I don't think there is that luxury and you may need to adjust your sheets - so it may be better not to use them.
Does anyone know if we have to use Vlookups? At work I have always used a combination of index and match functions, which allow you to insert columns in the source data without changing the result of the formula. This doesn't seem to be a commonly used practice, but I have always found it to be more flexible, and easier to follow if you name the areas being referenced. Will I lose marks for not going with the Vlookups that they seem to like so much?
