โครงสร้างและการใช้งาน XLOOKUP

การใช้งานฟังก์ชันหรือสูตร XLOOKUP จะมีโครงสร้างประมาณนี้

=XLOOKUP(ค่าที่ต้องการค้นหา, คอลัมน์ที่ค้นหา, คอลัมน์ผลลัพธ์, [ถ้าไม่พบ], [โหมดการเลือกข้อมูล], [โหมดการค้นหา])

ตัวโครงสร้างของฟังก์ชัน XLOOKUP จะแตกต่างไปจาก VLOOKUP ประมาณนึง แต่ยังใช้งานได้ง่ายพอ ๆ กันเลย ถ้าอธิบายตรงนี้ก็อาจจะงง ๆ นิดหน่อย เดี๋ยวไปดูตอนใช้งานเลยน่าจะเห็นภาพมากกว่า

สมมติว่าเรามีตัวอย่างตารางข้อมูลแบบเดิม

ถ้าเราอยากค้นหาเหมือนเดิมคือ "พนักงานที่มีรหัส TNJ0003 ชื่ออะไร"
เราก็จะสามารถเขียนสูตร XLOOKUP เพื่อค้นหาได้แบบนี้

=XLOOKUP("TNJ0003", A2:A4, B2:B4)

ผลลัพธ์ที่ได้ : นายสมหมาย ปลายทาง

พอลองเขียนสูตรเพื่อค้นหาจริง ๆ เราก็จะเริ่มความแตกต่างระหว่าง XLOOKUP กับ VLOOKUP

1. ระบุช่วงข้อมูลที่ใช้ค้นหาเป็นแค่ช่วงคอลัมน์เดียว ไม่ต้องเลือกทั้งตาราง

2. ระบุช่วงข้อมูลที่ต้องการผลลัพธ์เป็นช่วงคอลัมน์เดียว แทนการระบุเลขลำดับ

3. ไม่ต้องใส่โหมดการค้นหา​ (แต่ยังสามารถใส่ได้)

แต่นี่ก็ยังไม่ใช่ทั้งหมดที่ทำให้เราหันมาใช้งาน XLOOKUP แทน VLOOKUP

จุดที่ทำให้ XLOOKUP เหนือกว่า VLOOKUP

1. ค้นหาแบบย้อนกลับได้

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

=XLOOKUP("นายสมหมาย ปลายทาง", B2:B4, A2:A4)

ผลลัพธ์ที่ได้ : TNJ0003

2. สูตรไม่พังเวลา เพิ่ม/ลด คอลัมน์

สมมติว่าเรามีการเพิ่มคอลัมน์ให้กลายเป็นแบบนี้

และสมมติว่าเราเคยเขียนสูตร VLOOKUP ไว้แบบนี้

=VLOOKUP("TNJ0003", A2:B4, 2, FALSE)

ผลลัพธ์ที่ได้ : Business

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

แต่ถ้าเราเขียนสูตรด้วย XLOOKUP ไว้แบบนี้

=XLOOKUP("TNJ0003", A2:A4, B2:B4)

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

=XLOOKUP("TNJ0003", A2:A4, C2:C4)

ทำให้สูตรการค้นหายังคงทำงานได้ถูกต้องและได้ผลลัพธ์เป็น "ชื่อพนักงาน" ตามเดิม

3. สามารถตั้งค่าข้อความกรณีที่ไม่เจอข้อมูลได้

สมมติว่าถ้าเราอยากตั้งค่าข้อความเมื่อค้นหาข้อมูลไม่เจอว่า "ไม่พบข้อมูล" เราสามารถเพิ่มเข้าไปใน XLOOKUP ได้แบบนี้

=XLOOKUP("TNJ0004", A2:A4, C2:C4, "ไม่พบข้อมูล")

ผลลัพธ์ที่ได้ : ไม่พบข้อมูล

4. เลือกลำดับการค้นหาได้ (เอาค่าแรกสุด/ค่าหลังสุด)

กรณีที่บางตารางมีการบันทึกข้อมูลบางอย่างซ้ำ ยกตัวอย่างเช่นตารางการบันทึกเวลาเข้า-ออกของพนักงานแบบนี้

สมมติว่าถ้าเราอยากค้นหา "เวลาเข้างานล่าสุดของพนักงานรหัส TNJ0001"
ถ้าเป็น VLOOKUP จะไม่สามารถค้นหาได้ เพราะต้องดึงตัวแรกสุดเสมอ
แต่ถ้าเป็น XLOOKUP เราสามารถกำหนดได้แบบนี้

=XLOOKUP("TNJ0004", A2:A4, C2:C4, "ไม่พบข้อมูล", 0, -1)

ผลลัพธ์ที่ได้ : 09:03

โดยเลข 0 หมายถึงให้เอาตัวที่ตรงตามที่ค้นหาเป๊ะ ๆ ส่วนเลข -1 หมายถึงให้ค้นหาจาก หลังมาหน้า(last-to-first) นั่นเอง ซึ่งถ้าเราจะไม่เขียนเลข 0 เพราะมันเป็น Default อยู่แล้วก็สามารถเขียนสูตรแบบนี้แทนได้

=XLOOKUP("TNJ0004", A2:A4, C2:C4, "ไม่พบข้อมูล",, -1)

คำถามพิเศษ: แล้วถ้ามีค่าซ้ำมากกว่า 2 ตัว แล้วอยากได้ค่าตรงกลางล่ะ XLOOKUP สามารถค้นหาได้มั้ย?

คำตอบคือ ไม่ได้นะคร้าบ!! //เสียงคุณปัญญาชิงร้อยชิงล้าน

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

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

การใช้งาน Excel ให้เก่ง ไม่ใช่แค่จำสูตรให้เยอะ แต่ต้องเข้าใจว่ากรณีแบบไหน ควรใช้สูตรอะไร

และถ้าใครอยากเก่ง Excel แบบเจาะลึกตั้งแต่สูตรพื้นฐานไปจนถึงทริคขั้นสูง?
Tech Ninja ก็มีหลักสูตรอัปสกิล Excel ตั้งแต่ Basic พื้นฐานไปจนถึง Advanced ขั้นสูงเลยน้า ✨

สนใจติดต่อได้ที่ Facebook: Tech Ninja
หรือโทร 02-096-2899 (ต่อ 0) ได้เลย