Monday, September 12, 2022
HomeGamificationHow to Use XLOOKUP in Google Sheets

How to Use XLOOKUP in Google Sheets


Do you wish to LOOKUP a price based mostly on one other worth? For instance, you could have the scholars title and wish to search for the mum or dad contact information? There are a couple of methods to perform this. The perform XLOOKUP lets you take a price in a desk of values and return the worth in one other column. Here is how one can use XLOOKUP in Google Sheets.

VLOOKUP

VLOOKUP is Vertical lookup and HLOOKUP is horizontal lookup. I take advantage of these so much. One use case I’ve for these features is to return XP values when utilizing Gamification with my college students. How many XP factors do they should stage as much as the following stage.

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 instance, I’ve a desk of gamification ranges. I begin with all the degrees for my gamification exercise. Next to that’s the quantity of XP you would want to succeed in that stage. The third column is the title of the extent. If a pupil has 200 XP, what stage are they?

200 XP factors is a SCOUT on this sport.

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

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

In this VLOOKUP method I’m saying take a look at cell A3. Look on the desk in vary D4:E21 and return the 2nd column. (True, it’s sorted information). However, I wished the LEVEL of the scholar not simply the title. In the previous I’d duplicate the column of XP into the 4th column of the desk so I may lookup the XP and return the extent. However, with XLOOKUP I don’t have to do these shenanigans.

How to Use XLOOKUP in Google Sheets

XLOOKUP lets you search for any worth in any column of desk, not simply the primary column. You can then return any column.

When doing Gamification XP fashions I favor to have the extent as the primary column. XLOOKUP permits me to try this. I lookup the worth of the quantity of XP. Then the column of XP in my desk. Unlike VLOOKUP, I don’t want to focus on all the desk, simply the column I’m wanting up. VLOOKUP was difficult to should depend what number of columns over the return column was. XLOOKUP is way simpler. I can merely spotlight the values that I wish to return.

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

Looking Up Student Information

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

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

In this desk I’ve 3 columns of pupil info and three columns of guardian info. On one other sheet I’ve a contact log. I’ve put the scholars e-mail deal with within the contact log and I wish to lookup the guardians e-mail as nicely. 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 Info’!C2:C11,’Contact Info’!E2:E11)

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

  • How to give emoji feedback in google docs

    Google Docs: How to Give Emoji Feedback

  • How to Use XLOOKUP in Google Sheets

  • BookWidgets Google Classroom Add-on by Alice Keeler

    Add-on the Fun with BookWidgets

  • How to copy a value to other sheets

    PUSH a Value to Other Sheets

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

The answer is always a spreadsheet

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments