ฟังก์ชัน XLOOKUP ใน Excel

ฟังก์ชัน XLOOKUP ใน Excel

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

ฟังก์ชันนี้สามารถใช้ค้นหาค่าจากคอลัมน์หรือแถวใด ๆ ก็ได้ และแสดงผลลัพธ์จากตำแหน่งที่ตรงกันได้อย่างแม่นยำ นอกจากนี้ยังสามารถกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้ ช่วยให้สูตรทำงานได้เสถียรมากขึ้นเมื่อใช้ในไฟล์งานที่ซับซ้อน

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

ประโยชน์ของฟังก์ชัน XLOOKUP ใน Excel

  • ใช้แทนฟังก์ชัน VLOOKUP, HLOOKUP, INDEX และ MATCH ได้ในสูตรเดียว
  • ไม่จำเป็นต้องจัดเรียงข้อมูลตามลำดับ
  • รองรับการค้นหาทั้งแนวตั้งและแนวนอน
  • กำหนดค่าหากไม่พบข้อมูลได้ด้วย if_not_found
  • รองรับการค้นหาแบบ exact match และ approximate match
  • สามารถค้นหาแบบย้อนกลับจากล่างขึ้นบนได้

รูปแบบการใช้งาน (Syntax)

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

อาร์กิวเมนต์ที่ใช้ในฟังก์ชัน XLOOKUP มีดังนี้

  • lookup_value – ค่าที่ต้องการค้นหา
  • lookup_array – ช่วงข้อมูลที่ต้องการค้นหาค่า
  • return_array – ช่วงข้อมูลที่ต้องการแสดงผลลัพธ์
  • [if_not_found] – (ไม่จำเป็น) ข้อความหรือค่าที่แสดงเมื่อไม่พบผลลัพธ์
  • [match_mode] – (ไม่จำเป็น) วิธีการจับคู่
    • 0: ค่าที่ตรงกันพอดี (ค่าเริ่มต้น)
    • -1: รายการที่ตรงกันพอดีหรือรายการถัดไปที่น้อยที่สุด
    • 1: รายการที่ตรงกันพอดีหรือรายการที่มากที่สุดถัดไป
    • 2: ใช้ wildcard (เช่น *, ?)
      • ? (เครื่องหมายคำถาม) แทนอักขระตัวใดก็ได้ ตัวอย่างเช่น sm?th จะค้นหา “smith” และ “smyth”
      • * (เครื่องหมายดอกจัน) แทนจํานวนอักขระใดๆ ตัวอย่างเช่น *east จะค้นหา “Northeast” และ “Southeast”
      • ~ (เครื่องหมายตัวหนอน) ตามด้วย ?, * หรือ ~ หมายถึง เครื่องหมายคําถาม เครื่องหมายดอกจัน หรือเครื่องหมายตัวหนอน ตัวอย่างเช่น fy06~? จะค้นหา “fy06?”
  • [search_mode] – (ไม่จำเป็น) วิธีการค้นหา
    • 1 คือ ทำการค้นหาตั้งแต่รายการแรกเป็นต้นไป (หากไม่ระบุ จะเป็นค่าตั้งต้น)
    • -1 คือ ทำการค้นหารายการสุดท้ายเป็นต้นไป
    • 2 คือ ค้นหาแบบ Binary Search แบบ Ascending (จำเป็นจะต้องเรียง lookup_array แบบ  Ascending)
    • -2 คือ ค้นหาแบบ Binary Search แบบ Descending (จำเป็นจะต้องเรียง lookup_array แบบ  Descending)

ตัวอย่างการประยุกต์ใช้งานฟังก์ชัน XLOOKUP ใน Excel

ตัวอย่างที่ 1: ค้นหาชื่อพนักงานจากรหัส

สมมติเรามีข้อมูลดังนี้

รหัสพนักงานชื่อพนักงาน
1001สมชาย
1002อารีย์
1003ปกรณ์

ต้องการค้นหาชื่อพนักงานที่มีระหัส 1002

สูตรที่ใช้

=XLOOKUP(1002, A2:A4, B2:B4)
ใช้ฟังก์ชัน XLOOKUP ใน Excel ค้นหาชื่อพนักงานจากรหัส
  • 1002 คือค่าที่ต้องการค้นหา (lookup_value)
  • A2:A4 คือช่วงที่สูตรจะใช้ค้นหาค่าที่ตรงกับ 1002 (lookup_array)
  • B2:B4 คือช่วงของค่าที่ต้องการแสดงผลลัพธ์ (return_array)
  • สูตรจะไล่ตรวจจาก A2 ลงไป
  • เจอค่า 1002 ที่เซลล์ A3 → สูตรจะคืนค่าจากเซลล์ B3 ซึ่งก็คือ “อารีย์”

ผลลัพธ์

ตัวอย่างที่ 2: ค้นหาราคาสินค้าโดยใช้ฟังก์ชัน XLOOKUP ใน Excel ร่วมกับ IFERROR

ข้อมูลสินค้า

สินค้าราคา (บาท)
ปากกา15
ดินสอ10
ยางลบ5

ต้องการค้นหาสินค้าตามชื่อที่ต้องการ ถ้าไม่พบให้แสดงข้อความ “ไม่พบสินค้า”

สูตรที่ใช้

=IFERROR(XLOOKUP("ไม้บรรทัด", A2:A4, B2:B4), "ไม่พบสินค้า")
ใช้ฟังก์ชัน XLOOKUP ใน Excel ร่วมกับ IFERROR
  • XLOOKUP("ไม้บรรทัด", A2:A4, B2:B4) จะค้นหาคำว่า “ไม้บรรทัด” ในช่วง A2:A4
  • สูตรไม่พบ “ไม้บรรทัด” ในรายการ → จึงส่งคืนค่าประเภท #N/A
  • IFERROR(..., "ไม่พบสินค้า") จะตรวจว่าผลลัพธ์จาก XLOOKUP มี error หรือไม่
  • เมื่อพบว่าเกิด error → จะแสดงข้อความ “ไม่พบสินค้า” แทนการแสดงข้อผิดพลาด

ผลลัพธ์

ไม่พบสินค้า

ค้นหาราคาสินค้าโดยใช้ XLOOKUP ร่วมกับ IFERROR

ตัวอย่างที่ 3: ค้นหาชื่อสินค้าแบบค้นหาจากล่างขึ้นบน

ข้อมูลสต๊อกสินค้า

วันที่สินค้า
01/05/2025ปากกา
02/05/2025ยางลบ
03/05/2025ปากกา

ต้องการค้นหาชื่อสินค้าแบบค้นหาจากล่างขึ้นบน ถ้าพบให้แสดงวันที่ออกมา ถ้าไม่พบให้แสดงคำว่า “ไม่พบ”

สูตรที่ใช้

=XLOOKUP("ปากกา", B2:B4, A2:A4, "ไม่พบ", 0, -1)
ใช้ฟังก์ชัน XLOOKUP ใน Excel ค้นหาชื่อสินค้าแบบค้นหาจากล่างขึ้นบน
  • "ปากกา" คือค่าที่ต้องการค้นหาในคอลัมน์สินค้า (B2:B4)
  • A2:A4 คือช่วงที่ต้องการให้แสดงผลลัพธ์ (คอลัมน์วันที่)
  • if_not_found กำหนดเป็น "ไม่พบ" (หากไม่เจอจะแสดงคำนี้)
  • match_mode ใช้ 0 หมายถึงต้องตรงกันจริงๆ
  • search_mode ใช้ -1 หมายถึงค้นหาจากล่างขึ้นบน
  • สูตรจะเริ่มตรวจสอบจาก B4 → พบ “ปากกา” → คืนค่าจาก A4 → ได้ “03/05/2025”

ผลลัพธ์

ค้นหาชื่อสินค้าแบบค้นหาจากล่างขึ้นบน

ตัวอย่างที่ 4: ค้นหาโดยใช้ Wildcard (สัญลักษณ์แทน)

ข้อมูลตัวอย่าง

รหัสผู้ใช้ชื่อ
001abcวรรณา
002xyzศรัญญา

ต้องการค้นหาชื่อพนักงานที่มีรหัส 001 ตามด้วยตัวอักษรกี่ตัวก็ได้

สูตร

=XLOOKUP("001*", A2:A3, B2:B3, "ไม่พบ", 2)
ใช้ฟังก์ชัน XLOOKUP ใน Excel ค้นหาโดยใช้ Wildcard
  • "001*" ใช้สัญลักษณ์ * แทนค่าทุกตัวอักษรหลังจาก “001”
  • A2:A3 เป็นช่วงที่ใช้ค้นหาค่า (lookup_array)
  • B2:B3 เป็นช่วงของค่าที่จะแสดงผล (return_array)
  • match_mode = 2 หมายถึง เปิดการค้นหาด้วย wildcard
  • สูตรตรวจเจอว่า “001abc” ใน A2 ตรงกับ “001*” → คืนค่าจาก B2 → ได้ “วรรณา”

ผลลัพธ์

ค้นหาโดยใช้ Wildcard (สัญลักษณ์แทน)

ตัวอย่างที่ 5: ใช้ฟังก์ชัน XLOOKUP ใน Excel ค้นหาคนที่ได้คะแนนสูงสุด

ข้อมูลคะแนนสอบ

ชื่อคะแนน
กิตติ75
ดารา85
วัฒนา80

ต้องการค้นหาค้นที่ได้คะแนนสูงสุด

สูตร

=XLOOKUP(MAX(B2:B4), B2:B4, A2:A4)
ใช้ฟังก์ชัน XLOOKUP ใน Excel ค้นหาคนที่ได้คะแนนสูงสุด
  • MAX(B2:B4) ให้ค่า 85 (คะแนนสูงสุดในช่วง B2:B4)
  • XLOOKUP(85, B2:B4, A2:A4) จะค้นหาค่าที่ตรงกับ 85 ใน B2:B4
  • พบว่า B3 คือ 85 → คืนค่าจาก A3 → ได้ “ดารา”

ผลลัพธ์

หมายเหตุ

  • ฟังก์ชัน XLOOKUP ใช้ได้ใน Excel 365 และ Excel 2019 ขึ้นไปเท่านั้น
  • ควรใช้ฟังก์ชัน IFERROR หรืออากิวเมนต์ if_not_found เพื่อป้องกัน error เมื่อไม่พบค่า
  • เมื่อใช้กับตารางขนาดใหญ่ ควรตรวจสอบประสิทธิภาพโดยหลีกเลี่ยงการใช้กับช่วงข้อมูลที่ไม่จำเป็น
  • ไม่ควรใช้ match_mode = 2 โดยไม่ระบุ wildcard อย่างถูกต้อง เช่น * หรือ ?

สรุป

ฟังก์ชัน XLOOKUP ใน Excel คือฟังก์ชันที่มีประสิทธิภาพและมีความยืดหยุ่นสูง ช่วยค้นหาข้อมูลในตารางได้แม่นยำและใช้งานง่ายกว่าฟังก์ชันเก่า ๆ อย่าง VLOOKUP หรือ HLOOKUP โดยสามารถกำหนดเงื่อนไขได้อย่างหลากหลาย ทั้งการค้นหาข้อมูลแบบตรงตัว แบบประมาณ หรือแบบใช้สัญลักษณ์แทน รวมถึงยังสามารถกำหนดค่าที่ต้องการให้แสดงเมื่อไม่พบผลลัพธ์ได้อีกด้วย เหมาะสำหรับงานที่ต้องใช้การดึงข้อมูลอย่างมืออาชีพ

ข้อมูลอ้างอิง: XLOOKUP function

Tags
แชร์เรื่องนี้

เรื่องที่น่าสนใจ