-2 – Performs a binary search where the data needs to be sorted in descending order. If not sorted, this can give error or wrong results 
2 – Performs a binary search where the data needs to be sorted in ascending order. Useful when you want to find the last matching value in the lookup_array -1 – Does the search from bottom to top. 1 – This is the default option where the function starts looking for the lookup_value from the top (first item) to the bottom (last item) in the lookup_array. – Here you specify how the XLOOKUP function should search the lookup_array. 2 – To do partial matching using wildcards (* or ~). 1 – Looks for the exact match, but if it’s found, returns the next larger item/value. -1 – Looks for the exact match, but if it’s found, returns the next smaller item/value. 0 – Exact match, where the lookup_value should exactly match the value in the lookup_array. – Here you can specify the type of match you want:. In case you don’t specify this argument, a #N/A error would be returned – the value to return in case the lookup value is not found. return_array – the array from which you want to fetch and return the value (corresponding to the position where the lookup value is found). lookup_array – the array in which you’re looking for the lookup value. lookup_value – the value that you’re looking for. XLOOKUP function can tale 6 arguments (3 mandatory and 3 optional): I cover these with some easy XLOOKUP examples later in this tutorial that will make it crystal clear. I expect XLOOKUP to be available on all Office 365 versions soon.ĭon’t worry if the syntax and argument look a bit too much. This will give you access to the XLOOKUP function. There would be an Office Insider program and you can click and join the Office Insider Program. In case you’re already on Office 365 (Home, Personal, or University edition) and don’t have access to it, you can go to the File tab and then click on Account. But as of now, you only get to use it if you’re on Office 365. I am also not sure if this would ever be released for prior versions or not (maybe Microsoft can create an add-in the way they did for Power Query). So, if you’re using prior versions of Excel (2010/2013/2016/2019), you won’t be able to use this function. #EXCEL MAC FUNCTION FOR IDENTIFYING UNIQUE VALUES HOW TO#
The power of this function will become even more clear as I deep dive into some XLOOKUP examples later in this tutorial.īut before I get into the examples, there is a big question – how do I get access to XLOOKUP? How to Get Access to XLOOKUP?Īs of now, XLOOKUP is only available for the users of Office 365. XLOOKUP is a function that allows you to quickly look for a value in a dataset (vertical or horizontal) and return the corresponding value in some other row/column.įor example, if you’ve got the scores for students in an exam, you can use XLOOKUP to quickly check how much a student has scored using the name of the student. It does everything VLOOKUP used to do, and much more.
XLOOKUP is a new function is Office 365 and is a new and improved version of the VLOOKUP/ HLOOKUP function.
Example 11: Find the Last Value in the Column. Example 9: Conditional Lookup (Using XLOOKUP with Other Formulas). 
Example 7: Approximate Match with XLOOKUP (Find Tax Rate).
Example 6: Find the Last Matching Value. Example 5: Nested XLOOKUP (Lookup in Multiple Ranges). Example 4: When Lookup Value is Not Found (Error Handling). Example 3: Two Way Lookup Using XLOOKUP (Horizontal & Vertical Lookup). Example 2: Lookup and Fetch an Entire Record.
0 kommentar(er)
