Ep.3.2 สอนใช้งานฟังก์ชั่น Excel 365 : ค้นหาข้อมูล ด้วย XLOOKUP แบบ 2 เงื่อนไขการค้นหา

Introduction

This tutorial will guide you through using the XLOOKUP function in Excel 365 to search for data based on two conditions. XLOOKUP is a powerful tool for retrieving information from a dataset efficiently, and mastering it can significantly enhance your data management skills in Excel.

Step 1: Understanding the XLOOKUP Function

Before using XLOOKUP, it's essential to understand its syntax and components:

• Syntax:

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

• Parameters:
  • lookup_value: The value you want to search for.
  • lookup_array: The range of cells that contains the value to search.
  • return_array: The range of cells containing the value to return.
  • if_not_found: Optional. The value to return if no match is found.
  • match_mode: Optional. Specifies how to match the lookup_value.
  • search_mode: Optional. Determines the search direction.

Step 2: Setting Up Your Data

To effectively use XLOOKUP for two conditions, you need to prepare your dataset. Follow these steps:

1. Organize the Data: Ensure your data is in a table format with distinct columns for each criterion.
2. Identify Conditions: Determine the two conditions for your search. For example, you might want to find a product by both its name and category.

Step 3: Constructing the XLOOKUP Formula

To perform a search with two conditions, you will need to combine the criteria. Here’s how:

1. Enter the Formula: In the cell where you want the result, start typing your XLOOKUP formula.
2. Combine Conditions: Use an array formula to combine the two conditions. For example:

   =XLOOKUP(1, (A2:A10="ProductName") * (B2:B10="CategoryName"), C2:C10)
   

   In this formula:
   • Replace A2:A10 with the range of your first condition.
   • Replace B2:B10 with the range of your second condition.
   • Replace C2:C10 with the range of values you want to return.

Step 4: Handling Multiple Results

If your search may yield multiple results, consider using a helper column to concatenate the two conditions:

1. Create a Helper Column: In a new column, combine the two conditions. For example:

   =A2 & "-" & B2
   

2. Adjust Your XLOOKUP: Update your XLOOKUP formula to reference the helper column for the combined conditions.

Step 5: Testing Your Formula

After entering the formula, test it with various inputs to ensure it returns the expected results. If not, check the following:

• Ensure there are no typos in your conditions.
• Verify that the ranges in your formula are correct.

Conclusion

You have now learned how to use the XLOOKUP function in Excel 365 to search for data based on two conditions. By following these steps, you can efficiently retrieve information tailored to specific criteria. As a next step, consider practicing with different datasets to further enhance your skills. For additional resources, download the example file from the provided link in the video description.