อย่าเพิ่งทิ้ง VLookup ไปใช้ XLookup เพียงแค่ฟังเขามาว่าสูตร XLookup สร้างขึ้นมาเพื่อใช้แทน VLookup
ก่อนอื่น ตอบได้ไหมว่า Vlookup กับ XLookup ใช้หลักการที่ต่างกันอย่างไรในการหาค่าจากตาราง
ภาพซ้ายเป็นตารางที่เรียงรหัสจากน้อยไปมาก ส่วนภาพขวาเป็นตารางที่ไม่ได้เรียงลำดับ บางส่วนเรียงบ้างไม่เรียงบ้าง
ปกติแล้วเมื่อมองแว้บแรกที่ตารางเก็บข้อมูล ไม่ได้มองที่การเรียงลำดับก่อนหรอก แต่ให้มองว่าข้อมูลใน column ที่จะใช้ค้นหานั้นเป็นอะไร ถ้าเป็นรหัสแบบในภาพนี้ ไม่ว่าจะเป็น XLookup หรือ VLookup ต้องเลือกวิธีค้นหาค่าแบบ Exact match เท่านั้น เพื่อทำให้หาคืนค่าที่ต้องการมาให้กับรหัสที่หาเจอเท่านั้น
ถ้าใช้รหัส a003x ในการค้นหาอย่างในภาพนี้ ต้องหาไม่เจอแล้วคืนค่า error ออกมาให้
แต่ถ้าเรื่องที่ใช้ค้นหาเป็นตัวเลข เช่น คะแนนสอบที่หาว่าได้เกรดอะไร หรือจำนวนสินค้าที่หาว่าอยู่ในช่วงราคาเท่าไหร่ ให้ใช้วิธีค้นหาแบบ Approximate match (หรือที่ผมใช้เรียกเป็นแบบ Approaching match) ซึ่งใน Excel รุ่นใหม่ๆจะใช้คำว่า Binary Search ... ศัพท์ใหม่อีกแล้ว ยังไม่แน่ใจว่าจะใช้แทนกันได้ 100% ไหม
คราวนี้จะมาทดลองให้ดูว่าถ้าใช้การค้นหาแบบ Approximate match หรือ Binary search โดยใช้รหัสที่ไม่มีบันทึกไว้จะเกิดอะไรขึ้น
หลักการค้นหาค่าแบบ Approximate match เป็นยังไง
- Column ของข้อมูลที่ใช้ค้นหาต้องเรียงจากน้อยไปมากเสมอ
- ถ้าหาค่าพบ ให้คืนค่าจากรายการที่อยู่ในแนวเดียวกันกับค่าที่ใช้หา
- ถ้าหาค่าไม่พบ ให้คืนค่าจากรายการของ column ที่เก็บค่าที่ใช้หา ซึ่งมีค่ามากที่สุด แต่ยังน้อยกว่าหรือเท่ากับค่าที่ใช้หา
สูตร VLookup ใช้ค่า a003x ได้คำตอบที่เป็นชื่อและจำนวนออกมาเป็น c กับ 30 เพราะว่าในการมองหาค่า Look ไปพบรหัส a004 มีค่าเกินกว่า a003x ไปแล้ว จึงไม่ใช้รายการของ a004 แต่จะกระเด้ง UP ขึ้นไปใช้รายการของ a003 ซึ่งเป็นค่าที่มากที่สุดที่ยังน้อยกว่าหรือเท่ากับ a003x
สูตร XLooup จะค้นหาเจอว่า a003x อยู่ระหว่างรหัส a003 กับ a004 พอใช้ match แบบ -1 ก็จะคืนค่าที่น้อยกว่ามาให้ จึงได้คำตอบเป็นรายการของรหัส a003 แต่ถ้าใช้ match แบบ 1 จะคืนค่าของรายการที่มากกว่ามาให้ ซึ่งเป็นรายการของ a004
ถ้าตามไม่ทันว่าเลข 1, -1, หรือ 2 ที่ใช้ในตัวอย่างนี้หมายถึงอะไรหรือทำงานอย่างไร ขอให้ดูรายละเอียดได้จากบทความก่อนหน้านี้ที่
https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/excel-expert-application/how-xlookup-was-built
หรือดูคำอธิบายของไมโครซอฟท์ได้จาก
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?WT.mc_id=M365-MVP-4000499
อยู่มาวันหนึ่งใครก็ไม่รู้จัดการแทรกรายการใหม่เข้าไปหรือจัดตำแหน่งรายการใหม่กลายเป็นไม่เรียงลำดับ ตามภาพขวามือ จะพบว่าสูตรเดิมที่สร้างไว้ให้คำตอบที่ไม่เหมือนเดิม แถมแปลกๆเสียอีก
สาเหตุที่ a003x หาคำตอบด้วย VLookup เป็นรายการของ a002 มาให้แทนเพราะใน column ที่เก็บรหัส a004 a003 a002 a005 a001 นั้น Excel ตรวจพบว่ารหัสเริ่มเรียงจากน้อยไปมากตั้งแต่ a002 ที่เรียงไปหา a005
ส่วน XLookup ก็ใช้วิธีการหารายการที่เริ่มเรียงจากน้อยไปมากแบบเดียวกันกับ VLookup จะค้นหาเจอว่า a003x อยู่ระหว่างรหัส a002 กับ a005 พอใช้ match แบบ -1 ก็จะคืนค่าที่น้อยกว่ามาให้ จึงได้คำตอบเป็นรายการของรหัส a002 แต่ถ้าใช้ match แบบ 1 จะคืนค่าของรายการที่มากกว่ามาให้ ซึ่งเป็นรายการของ a005
นี่แหละที่ทำให้ต้องย้ำแล้วย้ำอีกว่าในตารางที่เก็บข้อมูลนั้น ก่อนจะไปแก้ไขตำแหน่งรายการให้ต่างไปจากเดิมต้องระวังด้วยว่าสูตรที่สร้างไว้จะเพี้ยนได้ด้วย ทางที่ปลอดภัยที่สุดห้าม Insert / Sort / Delete รายการเด็ดขาด มีแต่เพิ่มรายการใหม่ต่อท้ายรายการด้านล่างเท่านั้น และถ้าเดิมเป็นตารางที่เรียงจากน้อยไปมากเอาไว้ รายการใหม่ที่เพิ่มต่อท้ายก็ต้องเรียงต่อกันไปด้วย
หากคิดจะใช้ XLookup ต้องเข้าใจวิธีคิดของสูตรนี้อย่างถ่องแท้ ทุกอย่างที่มีข้อดี ย่อมมีข้อเสียและข้อจำกัด ยิ่งสูตร XLookup มีการกำหนดเงื่อนไขในสูตรได้หลายแบบ ยิ่งต้องระมัดระวังการใช้งานมากขึ้น
