Main Article ~ Online Business Tip ~ Excel Tip ~ Training Tip
Welcome. We wish you all the very best for the Season –and safe travelling, if you are travelling. A very big thank you for your support during the year! You have been loyal customers and friends.
We wish you and your loved ones a happy, safe, and festive season. And a prosperous 2024!
Our offices will be closed on all the upcoming public holidays. We will also officially be closed for the week between the Christmas and New Year's Day holidays, but you will be able to find us if you have an emergency.
For the Festive Season, we have –somewhere in the newsletter– valuable free gifts you can download.
Our eShop has good deals for you on new AutoCAD and LT subscriptions. Last month's (gasp!) price drop on Autodesk products is still active..
In the latest Excel Tip, we have a simple example on INDEX(MATCH), the alternative to VLOOKUP and similar functions.
Excel Macros and Excel for Engineers are ready for you as self-paced online courses.
Computius always has something profound to add.
There were no black Friday specials from Autodesk. But last month's price decrease is still in force on our range of Autodesk products: AutoCAD including specialized toolsets, AutoCAD LT, AutoCAD Revit LT Suite, and Maya LT. This apples to subscription renewals and new subscriptions.
You can get new subscriptions at a discount from our Online Shop.
For renewals, contact us now!
We cannot predict what the Rand will do, but the long-term trend has been a decline against the dollar, pushing our prices up. Might this be a good time to buy or renew before prices go up again?
Please email Software Africa if you need help.
For our geotechnical people, an improved version of HotPlot, the user-friendly Windows interface for dotPLOT, is in testing. Release should happen in the next month.
If you lost your phone –or if it was stolen– would you lose all your contacts? That data could be worth many times the value of your phone, and whether you can insure for the loss is dubious. Imagine having to recreate your entire phone list from scratch!
Last month we told you how to backup your Samsung Smartphone. But Apple is even easier:
How to back up your iPhone or iPad with iCloud:
Remember to back up reasonably often, say once a month, depending on how much you are willing to lose. As mentioned here, you can make this happen automatically: Isn't that smarter than Samsung?
Not for engineers alone, the Excel for Engineers course is available online as a self-paced course. The same manual and examples as the live version. The same trainer. Done at your own pace at times that suit you. Take half an hour a day and complete it in a month. Spend an hour a day and finish it in two weeks. Or dedicate two days –a weekend, perhaps?– and crack the whole course.
Sign up now and get these Bonuses:
Take our self-paced online course in your own time and venue. All the value at a quarter of the price of the live course. Backed by a 30-day money-back guarantee.
In an earlier issue we talked about INDEX(MATCH) instead of a LOOKUP Function, and the syntaxes (yes, plural!) of INDEX(). After all of that, you may feel overwhelmed. Not a good thing, particularly at this time of year.
You may feel that you could never use INDEX(MATCH): It looks soo complicated! Well, here is the very simplest example, which anyone can master (or mistress?). Here we are only looking for an exact match. For simplicity, we use one-column arrays only.
MATCH returns the position number of the matched item in a one-dimensional list. MATCH(lookup_value, lookup_array, 0) does an exact match. (MATCH can also do an approximate match, which we are ignoring for now.)
INDEX returns the value at a given row number in a range. Use INDEX(array, row_num) where row_num is the result of a MATCH. (INDEX can do columns too, but we are ignoring that in this article.)
In the example above, in rows 15 to 18, column A contains the results of the formula as documented in column B.
In cell A15, =MATCH("Gizmo", A4:A10, 0) looks down the range A4:A10 for an exact match. It finds "Gizmo" in the fifth position, and returns the value 5. Note that this is its number in the list, not the row number in the spreadsheet (8)!
In cell A16, =INDEX(E4:E10, A15) then uses the previous result to return the value 1463. It is the 5th item in the range E4:E10.
In cell A18 we show the whole process in one cell, using =INDEX(E4:E10, MATCH("Gizmo", A4:A10, 0))
Nesting the MATCH functions inside the INDEX makes the formula more compact. But the intermediate calculation makes the process easier to understand. It is also easier to debug!
The lookup range that MATCH uses does not have to be next to –or indeed anywhere near– the range from which INDEX gets the final value.
Notice also that we do not include the column headings in the lookup ranges: A4:A10, not A3:A10, E4:E10, not E3:E10
View this full post with more examples here.
If you aren’t sure whether to use the INDEX and MATCH functions together, or rather VLOOKUP, HLOOKUP, or LOOKUP, see this decision tree.
That completes "using INDEX(MATCH)". Next time: A new topic for a New Year!
Why not do the Excel Macros online course over the Holidays? Learn to do repetitive work in a flash, instead of repeating the same boring stuff by hand. Save hours not working late, and spend more time with your family... The Software Africa Quick 'n Easy Turbo-Start Excel Macros course is online. Take it now and avoid the inevitable price rises in the New Year.
Our website has free spreadsheets for you:
Enjoy your presents! Want automated spreadsheets like those? We can write them for you.
All you need to know is the user interface.
All the Best from
Communication in Action cc trading as Software Africa
"Empowering African Business with standard and custom PC programs, databases, and templates using Microsoft technologies"
Reg. 2009/007863/23 ~ VAT No 4500104387 ~ 126 Kelvin Drive, Morningside Manor, Sandton, 2191 South Africa.
Tel: 011 802-6440 ~ Cell: 082 389-3482 ~ e-mail: info@softwareafrica.co.za
Disclosure: Some links we share might be affiliate links we promote in return for a commission. We only promote things we've actually tried and think could be great for you too.
|
This entire newsletter is Copyright © 2023 Communication in Action cc t/a Software Africa. All rights reserved. Information may be reproduced in full context as long as credit is given. Newsletter Index | < January 2017 | < January 2018 | < January 2019 | < January 2020 | < January 2021 | < January 2022 | < January 2023 | < February | < March | < April | < May | < June | < July | < August | < September | < October | < November | Back to top | Updated 14 December 2023 | e-mail Webmaster. |