K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS >
      • ALAN SALMON
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS >
      • ALAN SALMON
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Excel's XLOOKUP Feature Begins Arriving

1/6/2020

0 Comments

 
Picture
Microsoft has begun rolling-out Excel’s anticipated XLOOKUP feature to some Office 365 subscribers. While not all Office 365 subscribers yet have access to this feature, if you are an Office 365 subscriber, you can expect receive this feature during 2020.  And once you do gain access to it, you will no doubt want to take full advantage of this great new enhancement to Excel.

XLOOKUP is alternative to VLOOKUP, HLOOKUP, and INDEX. While these three functions will remain in Excel, many users will find XLOOKUP to be simpler, more intuitive, and even more powerful. Some of the key differences between XLOOKUP and other lookup functions include:

  • XLOOKUP defaults to an exact match, whereas VLOOKUP and HLOOKUP default to an approximate match.
  • With XLOOKUP you do not have to specify a column index number as you do with VLOOKUP or a row index number as you do with HLOOKUP.
  • The arrangement of columns and rows does not matter with XLOOKUP because it can look to the left or right when using it as alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP.
  • XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR

An Example

To begin to understand the advantages of working with XLOOKUP, consider the example presented in Figure 1. In this illustration, XLOOKUP is used to find the value from cell H2 in the range of B3 through B15. Keep in mind, this function defaults to an exact match. Once it finds the value it is looking for, it returns the corresponding value from the range D3 through D15. If no match is found, then the formula would return the phrase “Item Not Found.”
Picture
Figure 1 - Simple Example of XLOOKUP-based Formula
Now compare the formula in Figure 2 to the one in Figure 1. See how the values to return are in the column to the left of values in the lookup column. This type of formula would not have been possible with a VLOOKUP function.
Picture
Figure 2 - XLOOKUP-based Formula Finding Values to the Left of the LOOKUP Range

Summary

Not all Office 365 subscribers yet have access to XLOOKUP. However, the impending general release of the new feature is creating a lot of conversation. XLOOKUP is considered by most to be a superior option to VLOOKUP, HLOOKUP, and INDEX. Accordingly, as you gain availability to this terrific new function, you will no doubt want to incorporate it into your Excel toolbox.

To learn more about this and other Excel features, consider some of the Excel-focused learning sessions available from K2E Canada Inc. For information, visit www.k2e.ca/training


Tommy Stephens

0 Comments



Leave a Reply.

    Categories

    All
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Business Intelligence
    Cloud Computing
    Collaborate
    Collaboration
    COVID 19
    COVID-19
    CPA
    Cybersecurity
    Ecommerce
    Excel
    Google
    Grammarly
    Internal Priorities
    KPIs
    LET
    Microsoft 365
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    PivotTables
    Power BI
    PowerPoint
    Productivity
    Ransomware
    Security
    Small Business Accounting Software
    Technology
    Tech Update
    Windows
    Windows 10
    Word
    Work At Home

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Alan Salmon
    Alan Salmon is recognized as Canada’s leading analyst in the area of accounting technology. He has nearly 35 years of business, management systems, education and journalism experience, has a degree in Science and an Advanced Teaching Certificate from the University of Toronto. Alan has now retired from teaching and lives in Brampton, Ontario with his wife Nancy. He can be reached at alan@k2e.ca

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com

    RSS Feed

Training & Education

Webinars
Upcoming Events
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  591 Highway 221  |  North Kingston, NS Canada B0P 1R0  |  (902) 200-9207
© 2020 K2E Canada Inc. ALL RIGHTS RESERVED.