Xlookup is powerful Excel function that allows you to search tables and return desired values. The cells it creates can be dynamic based on reference ranges. In this article, I will teach you how to use Xlookup. I am using the free version of Office 365 Excel for this example, but other versions will work just the same. I am also using the Student Information Dataset from Kaggle for this example.
Let us say we want to be able to look up student information based on the Student ID Number. In this case, we want to be able to type in the ID, have Excel search the table and find the entire row for that ID. We can use the Xlookup function to achieve this.
Xlookup is defined as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
For the Student Information Dataset, I fill in these values as follows:
lookup_value is the dynamic cell where I can input the Student ID that I want to look up.
lookup_array is the column that contains all the student IDs
return_array is the rest of the table, except for the ID column.
I leave the rest of the optional values blank, giving me the following function:
=XLOOKUP(I6,A3:A202,B3:G202,,,)
Now when I type in a Student ID Number into the I6 cell, Excel searches returns the corresponding row from the table.
Xlookup also has more advanced features like custom error messages and the ability to use wildcards. More information on this function can be found here.
Leave a Reply