by ·6 Comments
XLOOKUP Function

I personally don’t know why it took this long for Microsoft Excel team to create XLOOKUP function. The fact that VLOOKUP is considered to be one of Excel’s most widely used functions reflects a strong demand in string look up tabulations. Surely, a multitude of VLOOKUP‘s limitations can be overcome with patience, helper columns, INDEX/MATCH, CHOOSE, OFFSET and other constructs. Yet, why would we use any workarounds, when we would rather utilize a more powerful function with multitude of applications? Meet, much anticipated XLOOKUP function, which was officially released to Office 365 subscribers in early February of this year. It offers a really long list of additional benefits; in today’s tutorial we will review 11 scenarios that take full advantage of the following XLOOKUP features:

  • LEFT lookup
  • Horizontal lookup
  • Multi-cell/array retrieval
  • Match based on wildcard conditions
  • Combination of Vertical AND Horizontal lookups
  • Lookup based on multiple criteria
  • Lookup in reverse order
  • Lookup for maximum/minimum values
  • Built-in Error Handling
  • Exact match by default
  • Flexible approximate match

Continue reading

by ·0 Comments

Microsoft Excel personal macro workbook.


Personal Macro Excel Workbook

          We have already explored different Excel VBA macros here, here, and here, . While the power of Excel VBA is limited only by our creative imagination, the real limitation of a typical VBA code comes from the fact that it resides in the workbook where it was saved, and as such, can only be revoked while this file is open. This is where the personal macro workbook comes into play: if we save our code in this centralized place, we could use it in any Excel workbook on our local drive. This productivity hack would help us perform repetitive tasks (formatting and data presentation pet peeves, anyone) by automating them. The easiest way to save your code into PERSONAL.XLSB workbook is through recording a macro.

Continue reading

by ·0 Comments

Microsoft Excel’s CONVERT Function.


CONVERT Excel Function

          Microsoft Excel is one of those great software applications that empowers us to do a lot of different things. You can create art masterpieces , convert digital photos into pixel worksheets , create a cool animation , build a flight simulator , or play a game of Tetris . If you don’t feel ambitious enough to write pages upon pages of VBA code, but still want to do something practical with the program, perhaps you want to learn how to perform different conversion calculations in Excel? In fact, CONVERSION function enables you to convert weight, distance, time, pressure, force, energy, power magnetism, temperature, volume, liquid measure, area, information, and speed measures from one unit to another. The later version of Microsoft Excel you are running the more conversions you can perform. If you are working in Excel 2003 or earlier, this function is not available on earlier versions of Microsoft Excel.

Continue reading

by ·4 Comments

Calculate your age, WITHOUT using Excel’s DateDif function.


Calculate Your Age in Excel, without DATEDIF function

          My previous post on using Excel’s DATEDIF function resulted in rather productive discussions on LinkedIn. First of all, some users are concerned that the DATEDIF function will no longer be supported in Excel and will cease to exist at some point in the future. Microsoft openly states that “this function is provided for compatibility with Lotus 1-2-3.” , and given the history of this support, I don’t see why it will be discontinued. Nonetheless, I will make an attempt to replace DATEDIF function with other functions readily available in the program. Secondly, as a reminder that English is not the only language in the world, Microsoft created “local” versions of this function. A user confirmed existence of the SIFECHA function, but the syntax to use it, involves using semicolons, instead of commas. If you are using Spanish version of Excel, you might want to try both versions:

SIFECHA(fecha_inicial,fecha_final,unidad)
SIFECHA(fecha_inicial;fecha_final;unidad)

          According to this site , remaining versions of this function include:
Continue reading