The way to Use XLOOKUP in Google Sheets


Do you wish to LOOKUP a worth based mostly on one other worth? For instance, you will have the scholars title and wish to lookup the guardian contact data? There are a number of methods to perform this. The operate XLOOKUP lets you take a worth in a desk of values and return the worth in one other column. Right here is find out how to use XLOOKUP in Google Sheets.

VLOOKUP

VLOOKUP is Vertical lookup and HLOOKUP is horizontal lookup. I exploit these quite a bit. One use case I’ve for these features is to return XP values when utilizing Gamification with my college students. What number of XP factors do they should degree as much as the subsequent degree.

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

Need Extra Assist with This? Grow to be a Premium Member

Within the instance, I’ve a desk of gamification ranges. I begin with all the degrees for my gamification exercise. Subsequent to that’s the quantity of XP you would wish to achieve that degree. The third column is the title of the extent. If a pupil has 200 XP, what degree are they?

200 XP factors is a SCOUT on this sport.

In cell A3 I’ve the variety of XP factors. I wish to lookup the extent the scholar is. Nonetheless, what I lookup needs to be within the FIRST column for VLOOKUP.

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

On this VLOOKUP method I’m saying have a look at cell A3. Have a look at the desk in vary D4:E21 and return the 2nd column. (True, it’s sorted knowledge). Nonetheless, I needed the LEVEL of the scholar not simply the title. Prior to now I’d duplicate the column of XP into the 4th column of the desk so I may lookup the XP and return the extent. Nonetheless, with XLOOKUP I don’t must do these shenanigans.

The way to Use XLOOKUP in Google Sheets

XLOOKUP lets you lookup any worth in any column of desk, not simply the primary column. You’ll be able to then return any column.

When doing Gamification XP fashions I choose to have the extent as the primary column. XLOOKUP permits me to do this. I lookup the worth of the quantity of XP. Then the column of XP in my desk. Not like VLOOKUP, I don’t want to spotlight your entire desk, simply the column I’m wanting up. VLOOKUP was tough to should depend what number of columns over the return column was. XLOOKUP is far simpler. I can merely spotlight the values that I wish to return.

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

Trying Up Pupil Data

Whereas gamification is without doubt one of the enjoyable methods to make use of XLOOKUP and VLOOKUP, as a instructor I’m extremely more likely to wish to lookup details about a pupil.

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

On this desk I’ve 3 columns of pupil data and three columns of guardian data. On one other sheet I’ve a contact log. I’ve put the scholars e mail tackle within the contact log and I wish to lookup the guardians e mail as properly. In my desk, pupil emails are in column C and guardian emails are in column E. On my contact log I’ll use the method XLOOKUP to lookup the worth of e mail in cell D1

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

Discover the lookup values are on a unique sheet than the place I’m recording the log entry.

  • how to use NPC's to teleport in Minecraft.

    Minecraft: The way to Use NPC’s to Teleport

  • How to give emoji feedback in google docs

    Google Docs: The way to Give Emoji Suggestions

  • The way to Use XLOOKUP in Google Sheets

  • BookWidgets Google Classroom Add-on by Alice Keeler

    Add-on the Enjoyable with BookWidgets

Alice Keeler is the Queen of Spreadsheets. She likes to say that the reply is all the time a spreadsheet.

The answer is always a spreadsheet

Related Articles

Latest Articles