Have you ever needed to use multiple lookups to place data in a spreadsheet? Do you get tired of having to create multiple versions of the same lookup formula just because you need more than one piece of data? If so, the XLookup function in Office 365 may be just what you need. Xlookup has many more features than the old Vlookup and Hlookup, but this article will focus on the ability to return multiple pieces of data.
Xlookup may return multiple values in adjoining cells, either across or down. Say you have an employee list with the employees id, first name, last name and phone number and you want to retrieve all the data in one formula. Xlookup can do that.
For example, say you have an employee table is in cells q4:t53 and has the following attributes, id number, first name, last name and phone number and cell b3 three holds the id you want to retrieve. The formula to return the first name, last name and phone number is =XLOOKUP(b3,q4:q53, r4:t53). B3 is the value to search. Q4:Q53 is the ID column in the employee database that will be searched and r4:t53 asks to return three pieces of data.
Xlookup is a nice function, but unfortunately current it is only available in the Office 365 versions of Excel and not the Office 2019 or earlier versions.