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.

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.

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.

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