• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Use of VBA, array formulas Vlookup/Hlookup

C

capitalH

Member
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?
 
You must log in or register to reply here.
Back
Top