Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > XLOOKUP Google Sheets – 4 Best Alternatives!

XLOOKUP Google Sheets – 4 Best Alternatives!

This XLOOKUP in Google Sheets tutorial is suitable for users of Google Sheets

OBJECTIVE

Use the equivalent functions in Google Sheets to perform an Excel XLOOKUP on a dataset.

In this guide, I’ll cover

Table Of Contents
  1. OBJECTIVE
  2. XLOOKUP IN GOOGLE SHEETS EXPLAINED
  3. XLOOKUP IN GOOGLE SHEETS VIDEO TUTORIAL
  4. REPLACING XLOOKUP IN GOOGLE SHEETS
    • METHOD 1: USING INDEX/MATCH
    • METHOD 2:  USING VLOOKUP
    • METHOD 3:  USING QUERY
    • METHOD 4:  USING FILTER

XLOOKUP IN GOOGLE SHEETS EXPLAINED

XLOOKUP does not (yet!) exist in Google Sheets. XLOOKUP is a new, modern replacement for older lookup functions in Excel, such as VLOOKUP and INDEX MATCH, but it doesn’t exist in Google Sheets in the same way other lookup functions do.

Using XLOOKUP in Excel, you can find values in a table or range by row and return the corresponding value from another column.

In the screenshot below, we have used XLOOKUP in Excel to return the employee’s extension number in cell F4.

The lookup_value is the value in cell F4. The lookup_array is the range where the lookup value is found, A5:A15. The return_array is the range that contains the value you would like to return, C5:C15.

XLOOKUP in Excel
XLOOKUP in Excel

This handy and flexible lookup formula is great for Excel users. Unfortunately, XLOOKUP is not currently available in Google Sheets at the time of writing this blog. So, what is a viable alternative in Google Sheets? (xanax)

Well, the most common use of XLOOKUP is to look up a value in a table. With that in mind, there are four different ways you can produce a similar result: INDEX/MATCH, VLOOKUP, FILTER, and QUERY.

XLOOKUP IN GOOGLE SHEETS VIDEO TUTORIAL

XLOOKUP in Google Sheets Video Tutorial

REPLACING XLOOKUP IN GOOGLE SHEETS

In the following examples, we will be taking the basic arguments in XLOOKUP (not the optional arguments) and we’ll try to replace them:

=XLOOKUP(lookup_value,lookup_array,return_array)

METHOD 1: USING INDEX/MATCH

Combine the INDEX and MATCH functions in Google Sheets.

Start by indexing the range the contains the value you would like to return, in this case, the extension number. Then use the MATCH function to return the correct row using the lookup value in cell F3. (Ambien)

Add the FALSE or 0 arguments to the end to exactly match the name in cell F3 to the name in the table.

Use INDEX/MATCH in place of XLOOKUP in Google Sheets
Use INDEX/MATCH in place of XLOOKUP in Google Sheets

METHOD 2:  USING VLOOKUP

VLOOKUP in Google Sheets is very similar to VLOOKUP in Excel. Whilst XLOOKUP is an improvement to VLOOKUP. This trusty formula still gets the job done.

Start by specifying which value you want to look up in the table, cell F3. Next, select all of the data, A4:C14. Specify the column number that you would like to return. For us, we want to return the extension number, which is in column number 3.

Note: VLOOKUP numbers columns from left to right.

Finish off the formula with a FALSE argument to exactly match the employee name in the table.

Use VLOOKUP in place of XLOOKUP in Google Sheets
Use VLOOKUP in place of XLOOKUP in Google Sheets

METHOD 3:  USING QUERY

QUERY is one of the few functions that are available in Google Sheets and not in Excel. You can use QUERY to perform a lookup, but bear in mind this is a more sophisticated function and not as simple as some of the examples we have already seen.

The QUERY function enables you to write SQL queries on a Google Sheet to make things happen. Due to this, the way you write the QUERY formula to perform a look-up is different from the normal ‘formula language’ you might be used to using.

First, you need to specify the entire range of data, cell A2:C12. Next, you need to provide a SELECT statement to specify the column you would like to return, column C. Finally, you need to provide the query with the criteria, return column C when column A is equal to the value in cell F2.

This is fairly straightforward, but you must use single quotes to surround text strings and break the query as you reference the cell that contains the lookup value.

Use QUERY in place of XLOOKUP in Google Sheets
Use QUERY in place of XLOOKUP in Google Sheets

METHOD 4:  USING FILTER

In my opinion, the fastest, easiest, and closest formula to XLOOKUP is the FILTER function in Google Sheets.

Simply, specify the range that contains the value you would like to return, C2:C12. Then specify the criteria range, A2:A12 and then finally the criteria, F2.

Fast, simple and effective!

Use FILTER in place of XLOOKUP in Google Sheets
Use FILTER in place of XLOOKUP in Google Sheets

For more example and guidance on how to use functions and formulas in Google Sheets, please check out the following links below:

Ben Collins – 18 Google Sheets Formulas Tips & Techniques You Should Know

Envatotuts – How to Add Formulas & Functions in Google Spreadsheets

CIFL – 9 Google Sheets Formulas to Master

Take a look at more, free Excel training on Simon Sez IT.

excel skills
Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

What Is ClickUp? – An Ultimate Beginner’s Guide [2022]

Entering Credit Card Transactions in QuickBooks Pro 2013

How to Use Excel FORECAST Function? 2 Easy Ways

Using the Loan Manager in QuickBooks 2019

QuickBooks Training – A Small Business Beginner’s Guide [2022]

The Most Used Microsoft Teams Shortcuts – Download

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)