XLOOKUP

TLDR สรุปสั้นๆ

XLOOKUP ใช้ค้นหาและคืนค่าจากข้อมูลในรูปแบบตารางหรือช่วง ใช้งานได้ยืดหยุ่นมากกว่าฟังก์ชัน VLOOKUP

คำอธิบาย

XLOOKUP เป็นฟังก์ชันที่ใช้ในการค้นหาและดึงข้อมูลจากตารางหรือลำดับข้อมูลใน Excel โดยสามารถค้นหาได้จากข้อมูลในแถว ๆ หนึ่งแล้วดึงค่าที่เกี่ยวข้องจากแถวเดียวกันในคอลัมน์อื่น ๆ ได้ ฟังก์ชันนี้มีความยืดหยุ่นสูงมากกว่าฟังก์ชัน VLOOKUP

มีครั้งแรกในเวอร์ชันไหน

2021

รูปแบบคำสั่ง (Syntax)

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

Arguments

• lookup_value (Required – any)
  ค่าที่ต้องการค้นหาในตารางหรือช่วงข้อมูล
• lookup_array (Required – range or array)
  กลุ่มหรือช่วงข้อมูลที่ต้องการค้นหา
• return_array (Required – range or array)
  กลุ่มหรือช่วงข้อมูลที่จะส่งคืนค่าให้
• [if_not_found] (Optional – any)
  ข้อความที่จะคืนค่าในกรณีที่ไม่พบค่าที่ตรงกัน ถ้าไม่ระบุไว้จะคืนค่า #N/A
• [match_mode] (Optional – integer)
  เลือกวิธีการค้นหา: 0 – ค่าที่ตรงกันชัดเจน หากไม่พบคืนค่า #N/A; -1 – ค่าตรงกันชัดเจน แต่คืนค่าน้อยกว่า; 1 – คืนค่าที่มากกว่า; 2 – ค้นหาด้วยการหาแบบ Wildcard
• [search_mode] (Optional – integer)
  เลือกวิธีการค้นหา: 1 – ค้นหาจากรายการแรก; -1 – ย้อนกลับหาจากรายการสุดท้าย; 2 – ต้องเรียงลำดับจากน้อยไปมาก; -2 -ต้องเรียงจากมากไปน้อย

ตัวอย่างการใช้งาน (Examples)

• Formula:

  =XLOOKUP("TX0003", B3:B8, D3:D8)

  Description: ใช้ XLOOKUP แบบ Exact Match ค้นหา “TX0003” ในช่วงข้อมูล B3:B8 และดึงค่า Product Type จากช่วง D3:D8
  Result: “Book”
  
• Formula:

  =XLOOKUP("TX0003",TxData[OrderID],TxData[Product Type])

  Description: ใช้ XLOOKUP แบบ Exact Match ค้นหา “TX0003” ในคอลัมน์ OrderID และดึงค่าจากคอลัมน์ Product Type
  Result: “Book”
  
• Formula:

  =XLOOKUP("*oo*",TxData[Product Type],TxData[SalesAmt],"Not Found",2)

  Description: ใช้ XLOOKUP แบบ Wildcard (เลือก Match Mode เป็น 2 ในตอนท้าย) ค้นหารายการที่มีคำว่า “oo” ในคอลัมน์ Product Type และดึงค่าจากคอลัมน์ SalesAmt
  Result: 1100
  
• Formula:

  =XLOOKUP(DATE(2025,2,15),TxData[Date],TxData[SalesAmt],"Not Found",-1)

  Description: ใช้ XLOOKUP แบบ Approximate Match ค้นหาวันที่ใกล้เคียงที่สุดที่น้อยกว่าหรือเท่ากับ 15 กุมภาพันธ์ 2025 ในคอลัมน์ Date และดึงค่าจาก SalesAmt
  Result: 800
  

ตัวอย่างที่ XLOOKUP ทำได้ แต่ VLOOKUP ทำไม่ได้

• Formula: กำหนดได้ว่าถ้าไม่เจอข้อมูลให้ทำอะไร

  =XLOOKUP("TX9999",TxData[OrderID],TxData[Product Type],"Not Found")

  Description: ถ้าไม่เจอค่าในคอลัมน์ OrderID จะคืนค่า “Not Found” แทน (ซึ่งใน VLOOKUP จะได้ Error ว่า #N/A ทำให้ต้องใช้ IFERROR หรือ IFNA ครอบ)
  Result: “Not Found”
  
• Formula: ค้นหาดึงคอลัมน์ที่อยู่ด้านซ้าย

  =XLOOKUP(1200,TxData[SalesAmt],TxData[SalesRep])

  Description: ค้นหาว่าใครเป็น SalesRep จากยอดขาย 1200 โดย ใช้ XLOOKUP แบบ Exact Match ค้นหา 1200 ในคอลัมน์ SalesAmt และดึงค่าจากคอลัมน์ SalesRep
  Result: “Som”
  
• Formula: ดึงค่ากลับมามากกว่าหนึ่งคอลัมน์

  =XLOOKUP("TX0005",TxData[OrderID],TxData[[SalesRep]:[Qty]])

  Description: ดึงตั้งแต่คอลัมน์ SalesRep จนถึงคอลัมน์ Qty จาก OrderID = TX0005
  Result: {“Ple”,6}
  
• Formula: ใช้ XLOOKUP เพื่อให้ได้ผลลัพธ์ในฐานะของ Cell Reference (เช่นเดียวกับ INDEX)

  =SUM(XLOOKUP("TX0003",TxData[OrderID],TxData[Qty]):XLOOKUP("TX0005",TxData[OrderID],TxData[Qty]))

  Description: ใช้ XLOOKUP เพื่อให้ได้ผลลัพธ์ในฐานะของ Cell Reference ในลักษณะของ =SUM( XLOOKUPก้อน1 : XLOOKUPก้อน2)
  โดยดึงจำนวนสินค้าที่ขาย (Qty) ทั้งหมดที่อยู่ใน Range ระหว่าง Qty ของ OrderID “TX0003” และ Qty ของ “TX0005” มาหาผลรวม
  Result: ได้ 21 ซึ่งมาจาก SUM(F5:F7) หรือ 8+7+6 เพราะ XLOOKUP ตัวแรกค้นหา Qty ของ “TX0003” ได้ F5 , XLOOKUP ตัวที่สองค้นหา Qty ของ “TX0005” ได้ F7

Tips & Tricks

ผลลัพธ์ที่ได้จาก XLOOKUP จริงๆ แล้วเป็น Cell Reference ไม่ใช่แค่ดึงค่าใน Cell มาแบบ VLOOKUP ปกติ ดั่งที่แสดงในตัวอย่างสุดท้ายนี้ มันเกิดจาก =SUM( XLOOKUPก้อน1 : XLOOKUPก้อน2) นั่นเอง ซึ่งการดึงกลับมาเป็น Cell Reference แบบนี้สามารถนำไปช่วยสร้าง Dynamic Range ได้เช่นเดียวกับ INDEX

ข้อควรระวัง (Cautions)

ตรวจสอบ [lookup_array] และ [return_array] ให้มีขนาดเท่ากัน มิฉะนั้นจะเกิดข้อผิดพลาด #VALUE ตรวจสอบความเรียงลำดับข้อมูลเมื่อใช้ mode การค้นหาแบบ binary อย่างระมัดระวังเพื่อผลลัพธ์ที่ถูกต้อง

ฟังก์ชันที่เกี่ยวข้อง

• VLOOKUP
• HLOOKUP
• INDEX
• MATCH

References

• Microsoft XLOOKUP Documentation
• Ablebits XLOOKUP Guide
• CareerFoundry XLOOKUP Explanation

ขอบคุณที่เข้ามาอ่านนะครับ ❤️