Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

How to Use XLOOKUP in Google Sheets

Do you have a table of student data and need to lookup the guardian name for example? In this how to use XLOOKUP in Google Sheets Alice Keeler shows you how to return values in a table based on a look up value.

How to Use XLOOKUP in Google Sheets

Do you want to LOOKUP a value based on another value? For example, you have the students name and want to look up the parent contact info? There are a few ways to accomplish this. The function XLOOKUP allows you to take a value in a table of values and return the value in another column. Here is how to use XLOOKUP in Google Sheets.

VLOOKUP

VLOOKUP is Vertical lookup and HLOOKUP is horizontal lookup. I use these a lot. One use case I have for these functions is to return XP values when using Gamification with my students. How many XP points do they need to level up to the next level.

200 xp points and a table of values with the level of the game and the xp required and the title.

Want More Help with This? Become a Premium Member

In the example, I have a table of gamification levels. I start with all the levels for my gamification activity. Next to that is the amount of XP you would need to reach that level. The 3rd column is the title of the level. If a student has 200 XP, what level are they?

200 XP points is a SCOUT in this game.

In cell A3 I have the number of XP points. I want to look up the level the student is. However, what I look up has to be in the FIRST column for VLOOKUP.

=VLOOKUP(A3,D4:E21,2,true)

In this VLOOKUP formula I am saying look at cell A3. Look at the table in range D4:E21 and return the 2nd column. (True, it is sorted data). However, I wanted the LEVEL of the student not just the title. In the past I would duplicate the column of XP into the 4th column of the table so I could lookup the XP and return the level. However, with XLOOKUP I do not need to do these shenanigans.

How to Use XLOOKUP in Google Sheets

XLOOKUP allows you to look up any value in any column of table, not just the first column. You can then return any column.

When doing Gamification XP models I prefer to have the level as the first column. XLOOKUP allows me to do that. I lookup the value of the amount of XP. Then the column of XP in my table. Unlike VLOOKUP, I do not need to highlight the entire table, just the column I am looking up. VLOOKUP was tricky to have to count how many columns over the return column was. XLOOKUP is much easier. I can simply highlight the values that I want to return.

=XLOOKUP(A3,D4:D21,C4:C21)

Looking Up Student Information

While gamification is one of the fun ways to use XLOOKUP and VLOOKUP, as a teacher I am highly likely to want to lookup information about a student.

Table with student name, student ID, student email, guardian name, guardian email, and guardian phone.

In this table I have 3 columns of student information and 3 columns of guardian information. On another sheet I have a contact log. I have put the students email address in the contact log and I want to lookup the guardians email as well. In my table, student emails are in column C and guardian emails are in column E. On my contact log I will use the formula XLOOKUP to lookup the value of email in cell D1

=XLOOKUP(A2,’Contact Info’!C2:C11,’Contact Info’!E2:E11)

Notice the lookup values are on a different sheet than where I am recording the log entry.

  • 5 Easy Steps for Teaching with Digital Tools

  • Students Prepare to Present

    Help Your Students Prepare to Present

  • How Teachers Can 🛳️ Cruise to Less Stress with Tech

  • Infographic 7 Basic Steps for a Google Form

Alice Keeler is the Queen of Spreadsheets. She loves to say that the answer is always a spreadsheet.

The answer is always a spreadsheet

5 Easy Steps for Teaching with Digital Tools

Introducing digital tools into your teaching can seem challenging at first. There are many tools out there, and it might feel like there’s a lot to learn. But with easy steps for teaching with digital tools, you can start simply and gradually. There’s no need to dive in all at once – just take it one step at a time. Taking easy steps for teaching with digital tools starts with trying something!

Read More »
Students Prepare to Present

Help Your Students Prepare to Present

Maximize student success in presentations with ‘Speaker Notes by AliceKeeler,’ the ideal Google Slides add-on. Enhance how students prepare to present with easy transfer of speaker notes to Google Docs, promoting effective communication skills. Dive into the world of engaging, technology-aided presentations and empower students to shine in their academic endeavors. Discover the key to transforming student presentations into interactive, skill-building experiences.

Read More »

Infographic 7 Basic Steps for a Google Form

New to using Google Forms? This tool is essential for teachers to not only save time but to be adaptive to student needs. Use Forms to survey students, play games, personalize instruction, and assessment. This infographic on the 7 basic steps for a Google Form will help you get started.

Read More »

Online Workshop: Get Your Add-on Published

How can you make Google Workspace (Docs/Sheets/Slides) even better? You can create custom Add-ons with Google Apps Script. Wondering How to Get Your Google Workspace Add-on Published? Join Alice Keeler, Google Developer Expert, to learn the steps to get your Add-on officially published.

Read More »

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d