วิธีใช้ฟังก์ชัน XLOOKUP ใน Excel

ฟังก์ชัน Xlookup ใน Excelคืออะไร วิธีใช้ Xlookup ใน Excel มาหาคำตอบกับ LuckyTemplates.com กันเถอะ!

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

ฟังก์ชันนี้มีประโยชน์อย่างยิ่งเมื่อคุณต้องประมวลผลข้อมูลจำนวนมากในสเปรดชีต Excel ใน Excel คุณมีฟังก์ชัน LOOKUP หลายประเภท XLOOKUP เป็นหนึ่งในนั้น แล้วฟังก์ชัน XLOOKUP คืออะไร สูตร XLOOKUP คืออะไร และใช้งานอย่างไร มาหาคำตอบกับ LuckyTemplates.com กันเถอะ!

สิ่งที่ควรรู้เกี่ยวกับฟังก์ชัน XLOOKUP ใน Excel

• XLOOKUP คืออะไร?
  • วิธีการเข้าถึงฟังก์ชัน XLOOKUP
  • ไวยากรณ์ของฟังก์ชัน XLOOKUP
• ข้อดีและข้อเสียของฟังก์ชัน XLOOKUP ใน Excel
  • ข้อดีของฟังก์ชัน XLOOKUP
  • ข้อเสียของฟังก์ชัน XLOOKUP
• วิธีใช้ฟังก์ชัน XLOOKUP
  • ไม่มีหมายเลขดัชนีคอลัมน์เพิ่มเติม
  • ตรงทั้งหมดโดยค่าเริ่มต้น
  • XLOOKUP สามารถพบได้ทางด้านซ้าย
  • ใช้ XLOOKUP เพื่อค้นหาช่วง
  • XLOOKUP แทนที่ฟังก์ชัน HLOOKUP
  • XLOOKUP สามารถดูได้จากล่างขึ้นบน

ฟังก์ชัน XLOOKUP ใน Excel คืออะไร

โดยพื้นฐานแล้ว XLOOKUP จะเข้ามาแทนที่ฟังก์ชันเก่าๆ เช่น HLOOKUP, VLOOKUP และ LOOKUP รองรับการค้นหาข้อมูลทั้งแนวตั้งและแนวนอน

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

มีหลายวิธีในการเพิ่มการใช้ฟังก์ชัน XLOOKUP ให้ได้สูงสุด

วิธีการเข้าถึงฟังก์ชัน XLOOKUP

ปัจจุบัน XLOOKUP มีให้บริการสำหรับผู้ใช้โปรแกรม Insiders เท่านั้น ทุกคนที่เข้าร่วมโปรแกรม Insider จะสามารถเข้าถึงฟีเจอร์ล่าสุดของ Excel ได้เมื่อพร้อมใช้งาน Microsoft จะเปิดตัวฟังก์ชันนี้ให้กับผู้ใช้ Office 365 ทุกคนในเร็วๆ นี้ ขออภัย XLOOKUP ไม่พร้อมใช้งานสำหรับผู้ใช้ที่ใช้ Office เวอร์ชัน 2010, 2013, 2016 และ 2019

หากคุณไม่ได้ใช้ชุด Microsoft 365 บนคอมพิวเตอร์ของคุณ คุณอาจต้องอัปเกรดเป็น Microsoft 365 เพื่อเข้าถึงฟังก์ชัน XLOOKUP และถ้าคุณใช้ Microsoft 365 คุณจะเห็นตัวเลือกที่เปิดใช้งานใน Excel XLOOKUP ยังมีให้บริการบน Office 365 Online

ไวยากรณ์ของฟังก์ชัน XLOOKUP

ไวยากรณ์ของฟังก์ชัน XLOOKUP จะคล้ายกับไวยากรณ์ของฟังก์ชัน VLOOKUP และ HLOOKUP หากคุณเคยใช้มาก่อน คุณจะพบว่าการใช้ XLOOKUP สะดวกกว่า ต่อไปนี้เป็นไวยากรณ์สำหรับฟังก์ชัน XLOOKUP ใน Excel:

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

ฟังก์ชัน XLOOKUP รองรับอาร์กิวเมนต์ได้สูงสุด 6 อาร์กิวเมนต์ และนี่คือค่าของอาร์กิวเมนต์

1. lookup_value (จำเป็น): ค่าที่คุณต้องการค้นหา

2. lookup_array (จำเป็น): อาร์เรย์ที่คุณต้องการค้นหาค่าการค้นหา

3. return_array (จำเป็น): อาร์เรย์จากตำแหน่งที่คุณต้องการดึงข้อมูลและส่งกลับค่าเมื่อพบค่าการค้นหา

4. [if_not_found] (เป็นทางเลือก): เมื่อไม่พบรายการที่ตรงกัน นี่คือค่าที่ส่งคืน

5. [match_mode] (เป็นทางเลือก): อาร์กิวเมนต์นี้ช่วยให้คุณสามารถระบุประเภทการจับคู่ที่คุณกำลังมองหาได้ มีค่าที่แตกต่างกันเพื่อระบุ:

• 0 - ค้นหาการจับคู่แบบตรงทั้งหมดและค่าจะต้องตรงกับค่าในlookup_array ทุกประการ ค่านี้จะถูกตั้งเป็นค่าเริ่มต้นเมื่อไม่ได้กล่าวถึง
• -1 - ค้นหาค่าที่ตรงกันทุกประการ และเมื่อพบแล้ว จะกลับไปยังค่าที่น้อยกว่าถัดไป
• 1 - ค้นหาค่าที่ตรงกันทุกประการ และเมื่อพบจะส่งกลับค่าที่มากกว่าถัดไป
• 2 - ทำการจับคู่บางส่วนโดยใช้ไวด์การ์ด โดยที่*, ? และ~มีความหมายพิเศษ

6. [search_mode] (เป็นทางเลือก): ใช้เพื่อระบุโหมดการค้นหาของ XLOOKUP ในlookup_array มีค่าที่แตกต่างกันเพื่อระบุเหมือนกัน:

• 1 - ทำการค้นหาโดยเริ่มจากรายการแรก ค่านี้จะถูกตั้งเป็นค่าเริ่มต้นเมื่อไม่ได้ระบุอะไรไว้
• -1 - ทำการค้นหาแบบย้อนกลับโดยเริ่มจากรายการสุดท้าย
• 2 - ทำการค้นหาแบบไบนารีในlookup_arrayโดยที่ข้อมูลจะต้องเรียงลำดับจากน้อยไปหามาก หากข้อมูลไม่ได้รับการจัดเรียง อาจทำให้เกิดข้อผิดพลาดหรือผลลัพธ์ที่ไม่ถูกต้องได้
• -2 - ทำการค้นหาแบบไบนารีในlookup_arrayโดยที่ข้อมูลจะต้องเรียงลำดับจากมากไปน้อย หากข้อมูลไม่ได้รับการจัดเรียง อาจทำให้เกิดข้อผิดพลาดหรือผลลัพธ์ที่ไม่ถูกต้องได้

ข้อดีและข้อเสียของฟังก์ชัน XLOOKUP ใน Excel

XLOOKUP ยังคงเป็นฟังก์ชันที่ได้เปรียบเมื่อเทียบกับ VLOOKUP และ INDEX/ATCH แต่ก็มีข้อเสียในตัวเองเช่นกัน

ข้อดีของฟังก์ชัน XLOOKUP

• กรามทำงานได้ทั้งแนวตั้งและแนวนอน
• จำเป็นต้องมีเพียง 3 อาร์กิวเมนต์ แทนที่จะเป็น 4 อาร์กิวเมนต์เช่นใน VLOOKUP และ INDEX MATCH
• ตั้งค่าเริ่มต้นเป็นการทำงานแบบตรงทั้งหมดเสมอ
• การค้นหาบางส่วนสามารถทำได้โดยใช้ไวด์การ์ด
• การค้นหาสามารถดำเนินการตามลำดับจากมากไปน้อย
• ใช้หนึ่งฟังก์ชันแทนสองฟังก์ชันใน INDEX MATCH

ข้อเสียของฟังก์ชัน XLOOKUP

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

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

• XLOOKUP ทำให้สูตรที่ใช้มากที่สุดใน Excel ง่ายขึ้นและเกิดข้อผิดพลาดน้อยลง คุณเพียงแค่ต้องเขียน =XLOOKUP (ข้อมูลที่จะค้นหา, รายการ, รายการผลลัพธ์) และคุณจะได้รับคำตอบหรือ #N/A หากไม่พบค่า
• ผลลัพธ์การค้นหาเริ่มต้นนั้นตรงทุกประการ ข้อเสียประการหนึ่งของ VLOOKUP คือคุณต้องระบุ FALSE เป็นพารามิเตอร์สุดท้ายเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง XLOOKUP แก้ไขปัญหาดังกล่าวโดยดำเนินการจับคู่แบบตรงทั้งหมดตามค่าเริ่มต้น คุณสามารถใช้พารามิเตอร์โหมดการจับคู่เพื่อเปลี่ยนลักษณะการค้นหาได้หากต้องการ
• พารามิเตอร์ที่สี่รองรับกรณีที่ไม่พบค่า ในสถานการณ์ทางธุรกิจส่วนใหญ่ คุณจะถูกบังคับให้ "รวม" สูตรการค้นหาด้วย IFERROR หรือ IFNA เพื่อกำจัดข้อผิดพลาด XLOOKUP มีพารามิเตอร์ตัวที่ 4 เพื่อให้คุณสามารถทราบผลลัพธ์เริ่มต้นที่ต้องการได้หากไม่พบค่า
• XLOOKUP มีพารามิเตอร์การเลือกเพื่อค้นหาสถานการณ์พิเศษ คุณสามารถค้นหาจากด้านบนหรือด้านล่างด้วยอักขระพิเศษ และตัวเลือกที่เร็วกว่าในการค้นหาข้อมูลที่กรอง

วิธีใช้ฟังก์ชัน XLOOKUP

ดูตัวอย่างด้านล่างเพื่อทำความเข้าใจวิธีการทำงานของ XLOOKUP ในตัวอย่างนี้ เราจำเป็นต้องส่งคืนส่วนจากคอลัมน์ F สำหรับแต่ละ ID ในคอลัมน์ A

นี่คือตัวอย่างที่ใช้ค้นหาผลลัพธ์ที่แน่นอน แต่ฟังก์ชัน XLOOKUP ต้องการข้อมูลเพียงสามประเภทเท่านั้น

รูปภาพด้านล่างแสดง XLOOKUP ที่มี 5 อาร์กิวเมนต์ แต่จำเป็นต้องมีอาร์กิวเมนต์สามตัวแรกเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง เรามามุ่งเน้นไปที่พวกเขากันดีกว่า:

• Lookup_value : ค่าการค้นหา
• Lookup_array : ช่วงการค้นหา
• Return_array : ช่วงที่มีค่าที่จะส่งคืน

ในตัวอย่างนี้ เราจะใช้สูตร:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

ตอนนี้เรามาดูข้อดีบางประการของ XLOOKUP เหนือ VLOOKUP กัน

ไม่มีหมายเลขดัชนีคอลัมน์เพิ่มเติม

อาร์กิวเมนต์ที่สามของ VLOOKUP คือการระบุจำนวนคอลัมน์ข้อมูลที่จะส่งกลับจากช่วงตาราง นี่ไม่ใช่ปัญหากับ XLOOKUP อีกต่อไป เนื่องจากฟังก์ชันนี้อนุญาตให้ผู้ใช้เลือกช่วงส่งคืนได้ (คอลัมน์ F ในตัวอย่างนี้)

และอย่าลืมว่า XLOOKUP สามารถดูข้อมูลที่เหลือของเซลล์ที่เลือกได้ ซึ่งแตกต่างจาก VLOOKUP

คุณจะไม่พบข้อผิดพลาดของสูตรเมื่อเพิ่มคอลัมน์ใหม่ หากเกิดเหตุการณ์นี้ในสเปรดชีต ช่วงการส่งคืนจะถูกปรับโดยอัตโนมัติ

ตรงทั้งหมดโดยค่าเริ่มต้น

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

XLOOKUP สามารถพบได้ทางด้านซ้าย

ความสามารถในการเลือกช่วงการค้นหาทำให้ XLOOKUP มีความยืดหยุ่นมากกว่า VLOOKUP เมื่อใช้ XLOOKUP ลำดับของคอลัมน์ในตารางจะไม่สำคัญ

VLOOKUP ถูกจำกัดโดยการค้นหาคอลัมน์ซ้ายสุดของตาราง แล้วกลับมาจากจำนวนคอลัมน์ที่ระบุทางด้านขวา

ในตัวอย่างด้านล่าง เราจำเป็นต้องค้นหา ID (คอลัมน์ E) และส่งคืนชื่อของบุคคลนั้น (คอลัมน์ D)

ใช้สูตรด้านล่าง:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

ใช้ XLOOKUP เพื่อค้นหาช่วง

แม้ว่าจะไม่ธรรมดาเท่ากับการทำงานแบบตรงทั้งหมด แต่คุณสามารถใช้สูตรการค้นหาเพื่อค้นหาค่าภายในช่วงได้ ตัวอย่างเช่น เราต้องการคืนส่วนลดตามจำนวนเงินที่ใช้ไป

ครั้งนี้เราไม่ได้มองหาค่าเฉพาะเจาะจงและจำเป็นต้องรู้ว่าค่าในคอลัมน์ B อยู่ในช่วงใดในคอลัมน์ E เพื่อกำหนดส่วนลด

XLOOKUP มีอาร์กิวเมนต์ที่สี่ซึ่งเป็นทางเลือก (โปรดจำไว้ว่า อาร์กิวเมนต์นี้มีค่าเริ่มต้นเป็นการจับคู่แบบสัมบูรณ์) ที่เรียกว่า match_mode

คุณจะเห็นว่า XLOOKUP สามารถค้นหาการจับคู่โดยประมาณได้ดีกว่า VLOOKUP

มีตัวเลือกในการค้นหาการจับคู่ที่ใกล้เคียงที่สุดน้อยกว่า (-1) หรือการจับคู่ที่ใกล้เคียงที่สุดที่มากกว่า (1) ค่าการค้นหา นอกจากนี้ยังมีตัวเลือกให้ใช้ไวด์การ์ด (2) เช่น ? หรือ *. การตั้งค่านี้ไม่ได้เปิดใช้งานตามค่าเริ่มต้นเช่นเดียวกับใน VLOOKUP

สูตรในตัวอย่างนี้ส่งคืนค่าที่ใกล้เคียงที่สุดน้อยกว่าค่าการค้นหา หากไม่พบการจับคู่แบบตรงทั้งหมด:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

อย่างไรก็ตาม มีข้อผิดพลาดในเซลล์ C7 ควรส่งคืนส่วนลด 0% เนื่องจากใช้จ่ายไปเพียง 64 เท่านั้น ซึ่งไม่ตรงตามเกณฑ์ในการรับส่วนลด

ข้อดีอีกประการหนึ่งของฟังก์ชัน XLOOKUP ก็คือ ไม่จำเป็นต้องมีช่วงการค้นหาจากน้อยไปมาก เช่น VLOOKUP

ป้อนแถวใหม่ที่ด้านล่างของตารางการค้นหา จากนั้นเปิดสูตร ขยายขอบเขตการใช้งานด้วยการคลิกและลากมุม

สูตรจะแก้ไขข้อผิดพลาดทันที ไม่มีปัญหากับการมี 0 ที่ส่วนท้ายของตารางช่วง

XLOOKUP แทนที่ฟังก์ชัน HLOOKUP

ตามที่กล่าวไว้ ฟังก์ชัน XLOOKUP ยังสามารถแทนที่ฟังก์ชัน HLOOKUP ได้อีกด้วย ฟังก์ชันเดียวสามารถแทนที่สองฟังก์ชันได้ อะไรจะดีไปกว่านี้?

ฟังก์ชัน HLOOKUP จะค้นหาในแนวนอน ใช้ค้นหาตามแถว

ฟังก์ชันนี้ไม่โด่งดังเท่ากับ VLOOKUP แต่มีประโยชน์ในกรณีที่ชื่ออยู่ในคอลัมน์ A และข้อมูลอยู่ในแถวที่ 4 และ 5 เหมือนตัวอย่างด้านล่าง

XLOOKUP สามารถค้นหาได้ทั้งสองทิศทาง คอลัมน์ล่าง และตามแถว

ในตัวอย่างนี้ สูตรใช้ในการส่งกลับมูลค่าการขายที่เกี่ยวข้องกับชื่อในคอลัมน์ A2 โดยดูที่แถวที่ 4 สำหรับชื่อและส่งกลับค่าจากแถวที่ 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP สามารถดูได้จากล่างขึ้นบน

โดยปกติแล้วคุณจะต้องดูจากบนลงล่างในรายการเพื่อค้นหาค่าที่เกิดขึ้นครั้งแรก XLOOKUP มีอาร์กิวเมนต์ที่ห้าชื่อ search_mode อาร์กิวเมนต์นี้อนุญาตให้เปลี่ยนเส้นทางการค้นหาเพื่อเริ่มต้นจากด้านล่างสุดของรายการเพื่อค้นหาการมีอยู่ของค่าครั้งล่าสุด

ในตัวอย่างด้านล่าง เราจำเป็นต้องค้นหาระดับสินค้าคงคลังของแต่ละผลิตภัณฑ์ในคอลัมน์ A

ตารางการค้นหาจะจัดเรียงตามลำดับวันที่ เราต้องการคืนระดับสินค้าคงคลังจากการตรวจสอบครั้งล่าสุด (รหัสผลิตภัณฑ์ครั้งล่าสุด)

อาร์กิวเมนต์ที่ห้าของฟังก์ชัน XLOOKUP มีสี่ตัวเลือก ที่นี่เราจะใช้ ตัวเลือก ค้นหาจากหลังไปแรก

สูตรที่ใช้ในตัวอย่างนี้:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

ในสูตรนี้ อาร์กิวเมนต์ที่สี่จะถูกละเว้น เป็นทางเลือกและเราต้องการให้ค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด

ฟังก์ชัน XLOOKUP คือตัวตายตัวแทนที่คาดไว้ของทั้งฟังก์ชัน VLOOKUP และ HLOOKUP บทความนี้มีการใช้ชุดตัวอย่างเพื่อสาธิตข้อดีของฟังก์ชัน XLOOKUP

XLOOKUP เป็นการปรับปรุงที่ยอดเยี่ยมเมื่อพูดถึงฟังก์ชันการค้นหา ข้อจำกัดเพียงอย่างเดียวของฟังก์ชัน XLOOKUP คือไม่สามารถใช้งานร่วมกับเวอร์ชันย้อนหลังได้ หากคุณแชร์สเปรดชีตกับ Microsoft Office Suite เวอร์ชันเก่า คุณอาจได้รับข้อผิดพลาด

อย่างไรก็ตาม คุณสามารถเข้าถึง XLOOKUP ได้จาก Office 365 Online XLOOKUP ยังอยู่ในการพัฒนา ดังนั้นผู้ใช้ Excel จึงต้องใช้เวลาระยะหนึ่งจึงจะนำมาใช้

ขอให้คุณประสบความสำเร็จ!

ความแตกต่างระหว่าง XLOOKUP และ VLOOKUP

ทำไม XLOOKUP ถึงดีกว่า VLOOKUP

แม้ว่า VLOOKUP จะมีการใช้งานเป็นของตัวเอง แต่ก็มีคุณสมบัติที่จำกัดมากกว่า XLOOKUP ทำให้ XLOOKUP เป็นตัวเลือกยอดนิยมเมื่อต้องการประมวลผลข้อมูลจำนวนมาก ส่งผลให้ทำงานเร็วขึ้นและมีข้อผิดพลาดน้อยลง

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

เหตุผลอื่นๆ:

• XLOOKUP สามารถจัดการการแทรกหรือการลบคอลัมน์ได้
• XLOOKUP ใช้การค้นหาแนวตั้งและแนวนอน
• XLOOKUP ให้การจับคู่โดยประมาณที่ชาญฉลาดยิ่งขึ้น
• XLOOKUP ต้องการการอ้างอิงเซลล์น้อยลง