เจาะลึกฟังก์ชัน XLOOKUP: การค้นหาแบบ Multiple Criteria และ Native Error Handling

XLOOKUP คือฟังก์ชันที่เข้ามาปฏิวัติการค้นหาข้อมูลใน Excel โดยทำงานบนพื้นฐานของ Dynamic Array Engine บทความนี้จะเจาะลึกเทคนิคขั้นสูงที่ VLOOKUP ทำไม่ได้ โดยเฉพาะการค้นหาแบบหลายเงื่อนไข (Multiple Criteria) โดยใช้หลักการ Boolean Logic และการจัดการ Error โดยไม่ต้องพึ่ง IFERROR

1. Data Structure Setup (โครงสร้างข้อมูลตัวอย่าง)

เพื่อให้เห็นภาพการทำงานของสูตร เราจะอ้างอิงข้อมูลจากตาราง [Source_Data] นี้ตลอดบทความ:

2. Multiple Criteria: การค้นหาซ้อนเงื่อนไขด้วย Boolean Logic

โจทย์: ต้องการหาราคา (Price) ของ “Hoodie” ไซซ์ “M”

ในอดีตเราต้องสร้าง Helper Column เพื่อนำ Text มาต่อกัน แต่ใน XLOOKUP เราจะใช้เทคนิคการคูณ Array (Array Multiplication) แทน

The Formula (สูตร)

=XLOOKUP(1, (A2:A5="Hoodie") * (B2:B5="M"), C2:C5)

Logic Deep Dive: ทำไมต้อง lookup_value เป็น 1?

เบื้องหลังการทำงานของ Excel ใน Memory จะเกิดกระบวนการเปรียบเทียบตรรกะทีละบรรทัด ดังนี้:

Step 1: Evaluation (ตรวจสอบเงื่อนไขแยกกัน)

Array 1 (Product="Hoodie"): {TRUE; FALSE; TRUE; FALSE}
Array 2 (Size="M"):         {FALSE; TRUE; TRUE; FALSE}

Step 2: Boolean Math (การคูณตรรกะ)

ใน Excel: TRUE = 1 และ FALSE = 0 เมื่อนำมาคูณกันจะเป็นการทำ AND Operation:

Row 2: TRUE * FALSE  = 1 * 0 = 0
Row 3: FALSE * TRUE  = 0 * 1 = 0
Row 4: TRUE * TRUE   = 1 * 1 = 1  <-- (Match!)
Row 5: FALSE * FALSE = 0 * 0 = 0

Step 3: Final Lookup Array

สูตรจะแปลงสภาพเป็น:

=XLOOKUP(1, {0; 0; 1; 0}, C2:C5)

XLOOKUP จะวิ่งหาเลข 1 ตัวแรกที่เจอ ซึ่งตรงกับตำแหน่ง Row 4 (Hoodie, M) และคืนค่า 1,100 ออกมา

3. Native Error Handling: เลิกใช้ IFERROR ซ้อนสูตร

การใช้ IFERROR(VLOOKUP(...), "Not Found") เป็นการสิ้นเปลืองทรัพยากรคำนวณ (Computational Cost) เพราะ Excel ต้องรันสูตรหลักให้ Error ก่อนถึงจะทำงานส่วน Error Handler

XLOOKUP มี Argument ที่ 4 ชื่อ [if_not_found] มาให้ในตัว ซึ่งทำงานเร็วกว่าและเขียนสูตรสั้นกว่า

เปรียบเทียบ Syntax

แบบเก่า (VLOOKUP):

=IFERROR(VLOOKUP("Hat", A2:C5, 3, 0), "Out of Stock")

แบบใหม่ (XLOOKUP):

=XLOOKUP("Hat", A2:A5, C2:C5, "Out of Stock")

Advanced: Nested XLOOKUP (การค้นหาข้าม Table)

กรณีมีข้อมูลอยู่ 2 Table (Table_New และ Table_Old) เราสามารถซ้อน XLOOKUP ลงไปในช่อง [if_not_found] ได้เลย:

=XLOOKUP(
    lookup_val, 
    Table_New[ID], 
    Table_New[Price], 
    XLOOKUP(lookup_val, Table_Old[ID], Table_Old[Price], "Not in both tables") 
)

4. Two-Way Lookup: การค้นหาแบบตัดแกน (Matrix Lookup)

โจทย์: ต้องการหาค่าจากตารางที่มี Header เป็น “เดือน” (แนวนอน) และ Row เป็น “สินค้า” (แนวตั้ง)

โครงสร้างข้อมูล (Matrix):

      [A]       [B]    [C]    [D]
[1]   Product   Jan    Feb    Mar
[2]   Item_X    100    120    150
[3]   Item_Y    200    210    230

ต้องการหา: ยอดขายของ Item_Y ในเดือน Feb

The Formula (สูตรซ้อนสูตร)

=XLOOKUP("Item_Y", A2:A3, XLOOKUP("Feb", B1:D1, B2:D3))

Logic Breakdown

1. Inner XLOOKUP (ตัวใน): XLOOKUP("Feb", B1:D1, B2:D3)
   • ค้นหา “Feb” ใน Header แนวนอน
   • คืนค่ากลับมาเป็น Vertical Array ของเดือนนั้นทั้งคอลัมน์: {120; 210}
2. Outer XLOOKUP (ตัวนอก): XLOOKUP("Item_Y", A2:A3, {120; 210})
   • ค้นหา “Item_Y” ในคอลัมน์ Product
   • ดึงค่าจาก Array ผลลัพธ์ที่ได้จากข้อ 1
   • Result: 210

Summary Cheat Sheet