S1E4-XLOOKUP

Hi, welcome to the fourth episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of XLOOKUP. 

The XLOOKUP function is a powerful and versatile tool that allows you to look up values in a table or range by matching a lookup value. You can use the XLOOKUP function to perform exact, approximate, wildcard, or reverse lookups. Here is a step by step approach on how to use the XLOOKUP function in a paragraph format: 

• Open a blank Excel file and enter some sample data in a table or range. For example, you can enter the following data in cells A1:B5: 

| Name    | Score | 

|---------|-------| 

| Alice   | 90    | 

| Bob     | 80    | 

| Charlie | 70    | 

| David   | 60    | 

| Eve     | 50    | 

• Select a cell where you want to enter the XLOOKUP formula. For example, you can select cell D2. 

• Type =XLOOKUP( in the cell. You will see a tooltip that shows the syntax and arguments of the XLOOKUP function. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The arguments are: 

• lookup_value: The value you want to look up. For example, you can enter "Bob" as the lookup value to find his score. 

• lookup_array: The range or array that contains the lookup values. For example, you can enter A2:A6 as the lookup array to search for names. 

• return_array: The range or array that contains the values to return. For example, you can enter B2:B6 as the return array to return the scores. 

• [if_not_found]: (Optional) The value to return if the lookup value is not found. For example, you can enter "Not found" as the if_not_found argument to display a message if the name is not in the table. 

• [match_mode]: (Optional) A number that determines the type of match to perform. The default value is 0, which means an exact match. You can also use -1 for an exact match or the next smaller item, 1 for an exact match or the next larger item, or 2 for a wildcard match. 

• [search_mode]: (Optional) A number that determines the direction of the search. The default value is 1, which means a search from first to last. You can also use -1 for a search from last to first, 2 for a binary search from first to last (requires sorted data), or -2 for a binary search from last to first (requires sorted data). 

• So that completes the descriptions of the arguments. 

• Enents for the XLOOKUP function in the cell, separated by commas. You can enter =XLOOKUP("Bob",A2:A6,B2:B6,"Not found") in cell D2. This formula will look up the name “Bob” in the range A2:A6 and return the corresponding score from the range B2:B6. If the name is not found, it will return the message “Not found”. 

• Press Enter to complete the formula. You will see the result in the cell. For example, you will see 80 in cell D2, which is the score of Bob. 

• You can copy and paste the formula to other cells or modify the arguments to perform different lookups. For example, you can change the lookup value to “Eve” or “Frank” to see their scores or the message “Not found”. You can also change the match mode to 2 and use a wildcard character like * or ? to perform a partial match. For example, you can enter =XLOOKUP("D*",A2:A6,B2:B6,"Not found",2) in cell D3 to look up any name that starts with D and return the score. You will see 60 in cell D3, which is the score of David. 

That was a basic explanation of  XLOOKUP and how to use it in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.