George - Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5)

Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5) — read online for free the complete book (whole text) full work

Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5)" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

1234567...12

Table of Contents

Excel XLOOKUP and Other Lookup Functions

Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions

Nathan George

Other Excel Books by Author

Excel 2019 Basics: A Quick and Easy Guide to Boosting Your Productivity with Excel

Excel 2019 Advanced Topics: Leverage More Powerful Tools to Enhance Your Productivity

Excel 2019 Functions: 70 Top Excel Functions Made Easy

Excel 2019 Macros and VBA: An Introduction to Excel Programming

Copyright Nathan George 2020

All rights reserved. The right of Nathan George to be identified as the author of this work has been asserted by him in accordance with the Copyright, Designs and Patents Act, 1988. It is illegal to copy, distribute, or create derivative works from this book in whole or in part or to contribute to the copying, distribution, or creating of derivative works of this book.

***

The information given in this book is given in good faith and belief in its accuracy at the time of publication. The author and publishers disclaim any liability arising directly or indirectly from the use, or misuse, of the information contained in this book.

Introduction

XLOOKUP is a new and exciting function in Excel that supersedes VLOOKUP, HLOOKUP and even INDEX/MATCH. If you are new to Excel lookup functions or already familiar with previous lookup functions, you will learn easier and faster methods of creating lookup formulas.

In this book, we get to cover Excel lookup formulas in more depth, with more examples than would have been practical in a general-purpose Excel book. This book will walk you through multiple examples of how you can use XLOOKUP to create solutions for different scenarios in Excel, including specific examples to highlight some of XLOOKUPs differences and advantages over VLOOKUP.

Excel XLOOKUP and Other Lookup Functions also covers the new XMATCH function (which is a replacement for the old MATCH function) and how you can use INDEX/XMATCH/XMATCH to perform simultaneous vertical and horizontal lookups.

The FILTER and SORT functions are also new dynamic array functions that you can use to return multiple values based on your specified criteria.

The new dynamic array functions like XLOOKUP and FILTER are only available to Microsoft 365 subscribers. Thus, if you are using a perpetual licence version of Office 2019 or Office 2016, XLOOKUP will not be available to you. For that reason, this book also covers the good old VLOOKUP in some depth. VLOOKUP is still an immensely popular and widely used function in Excel.

After reading this book, you will know how to use the new XLOOKUP function to create solutions for a variety of lookup tasks in Excel. You will also learn how to use the combination of INDEX/XMATCH/XMATCH to perform complex lookups, and how to use the FILTER and SORT functions to fetch and transform data.

Who Is This Book For?

This book is for you if you want to learn more about the new XLOOKUP and XMATCH functions in Excel. We also cover the VLOOKUP function in case you still need it. This book is not an introductory Excel book and requires some basic knowledge of Excel. You need to be familiar with creating formulas with functions in Excel.

If you need to brush up on the basics (or if you are new to Excel), then my Excel 2019 Basics book covers all the foundation knowledge you will need. I have created the lessons in this book using Excel 2019 in the Microsoft 365 suit.

Assumptions

The software assumptions made when writing this book is that you already have Excel installed on your computer and you are working on the Microsoft Windows platform (7, 8 or 10). If you are using Excel on a Mac, then substitute any Windows keyboard commands mentioned in the book for the Mac equivalent. All the features within Excel remain the same for both platforms.

If you are using Excel on a tablet or touchscreen device, again substitute any keyboard commands mentioned in the book with the equivalent on your touchscreen device.

Important: If you are using Microsoft 365 (formally Office 365) and you do not have XLOOKUP or XMATCH in your Excel installation, dont panic, it is on its way! Your Microsoft 365 installation may be on a semi-annual update plan, in which case you will get the update starting July 2020.

Practice Files

Included with this book are downloadable Excel files for all examples covered. This will enable you to follow the examples in this book, hands-on, without needing to create the sample data from scratch. You can practice by changing the data to view different results.

Click the following link to download the Excel sample files:

https://ngdigital.s3.amazonaws.com/Xlookup.zip

Note : The practice files are Excel 2019 files. You would need to have Excel installed on your computer to open and use these files (preferably Excel 2013 and above). Also, the files have been zipped into one download. Windows 10 comes with the functionality to unzip files, but if your OS does not have this feature, you will need to get a piece of software like WinZip or WinRAR to unzip the file.

Chapter 1: How to Enter a Formula

To insert a formula/function:

1. Click in the cell where you want to display the result.
2. Click in the formula bar.
3. Enter your formula, starting your entry with the equal sign (=). This specifies that your entry is a formula and not a static value.

For example:

=SUM(A2:A10)

Tip : As much as possible, avoid typing cell references directly into the formula bar as it could introduce errors. To enter a function, type in the equal sign followed by the function, and then an open bracket. For example, enter =SUM( . Then using your mouse pointer, select the cells you want for the arguments in the worksheet itself before typing in the closing bracket.

The Insert Function Dialog Box

A second way you can enter a function is by using the Insert Function dialog box:

1. Select the cell where you want to insert the formula.
2. Click in the formula bar to place the cursor there and click the Insert Function button to the left of the formula bar (or the Insert Function command button on the Formulas tab on the Ribbon).
   
   This will display the Insert Function dialog box. This dialog box provides the option to search for the function or select it from a category.
3. To search for the function, enter the name of the function in the Search for a function box. For example, if you are searching for the XLOOKUP function, you would enter XLOOKUP in the search box, select it from the list below, and click the Go button.
   The Select a function list will display all the functions related to your search term.
   
   Note : If you do not have the XLOOKUP function yet, it will not show up on the list.
   You can also use the category drop-down list to select a function if you know its category in Excel. For example, you can find the XLOOKUP function in the Logical category (if you have it). If you have used a function recently, it will be listed in the Most Recently Used category.
   

Next page

1234567...12

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

Similar books «Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5)»

Look at similar books to Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5). We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.

Sanusi A. L.

Excel Vlookup: A Step by Step Visual Guide

Jelen

MrExcel 2022 - Boosting Excel

Bill Jelen

MrExcel 2022: Boosting Excel

Alan Murray

Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas

Ahamed Mohideen Mubeen

Most Essential Excel Formulas and Functions for Any Career: VLOOKUP, Excel Formulas and Functions

Sheikh

Microsoft Excel Advanced Functions and Formulas

Harjit Suman

Excel Formulas and Functions : the Complete Excel Guide for Beginners

Suman

Excel Formulas and Functions: The Step by Step Excel Guide on how to Create Powerful Formulas

Henry E. Mejia

Excel Vlookup Champion A Step by Step Complete Course to Master Vlookup Function in Microsoft Excel

John Michaloudis

101 Most Popular Excel Formulas

Ken Bluttman

Excel Formulas & Functions For Dummies

John Walkenbach

Excel 2013 Formulas

Discussion, reviews of the book Excel XLOOKUP and Other Lookup Functions: Create Easier and More Versatile Lookup Formulas with New Powerful Excel Functions (Excel 2019 Mastery Book 5) and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.