Ep.3 สอนใช้งานฟังก์ชั่น Excel 365 : ค้นหาข้อมูล ด้วย XLOOKUP ตัวจบของสายค้นหาข้อมูล

Introduction

In this tutorial, we will explore the XLOOKUP function in Excel 365, a powerful tool for searching and retrieving data efficiently. This function replaces older search functions like VLOOKUP and HLOOKUP, offering greater flexibility and ease of use. By the end of this guide, you’ll be equipped to utilize XLOOKUP to streamline your data retrieval tasks.

Step 1: Understand the XLOOKUP Function

Before using XLOOKUP, it’s important to grasp its syntax and purpose. The basic syntax is:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value you want to find.
• lookup_array: The array or range where the lookup value is searched.
• return_array: The array or range from which to return the result.
• if_not_found: (Optional) What to return if no match is found.
• match_mode: (Optional) Specifies how to match the lookup value.
• search_mode: (Optional) Defines the search direction.

Step 2: Prepare Your Data

1. Open Excel and ensure your data is organized in a table format.
2. Identify the column that contains the values you want to look up.
3. Confirm that the return column you want to extract data from is adjacent to the lookup column.

Step 3: Insert the XLOOKUP Function

1. Click on the cell where you want the result to appear.
2. Type =XLOOKUP( to begin the function.
3. Input the parameters:
   • For lookup_value, select the cell containing the value to search for.
   • For lookup_array, select the range of cells where the lookup value may exist.
   • For return_array, select the range of cells from which you want to return data.

Example:

=XLOOKUP(A2, B2:B10, C2:C10)

Here, A2 is the lookup value, B2:B10 is the lookup array, and C2:C10 is the return array.

Step 4: Customize the Function

• To handle cases where no match is found, add an optional if_not_found parameter.
  • Example: =XLOOKUP(A2, B2:B10, C2:C10, "Not Found")
• Adjust match_mode and search_mode as required to refine your search.

Step 5: Test the Function

1. Press Enter to execute the function.
2. Verify the result in the cell where you inserted the formula.
3. Experiment with different lookup values to ensure accuracy.

Common Pitfalls to Avoid

• Ensure there are no leading or trailing spaces in your lookup values.
• Remember that XLOOKUP is case-insensitive by default.
• Check that the lookup and return arrays are of the same size; otherwise, an error will occur.

Conclusion

You’ve now learned how to effectively use the XLOOKUP function in Excel 365 to streamline your data search and retrieval processes. This function not only simplifies data lookup but also enhances your ability to manage and analyze data efficiently. For further practice, consider downloading the provided Excel file from the video description to apply what you've learned in a hands-on manner. Happy Excel-ing!