"หาค่าเจอเสมอ" เป็นข้อผิดพลาดที่ร้ายที่สุดของการใช้สูตร VLookup แบบสั้นๆ
ไม่ว่าตารางข้อมูลจะเรียงหรือไม่เรียง ไม่ว่าจะใช้รหัส a003 หรือ a003x ซึ่งไม่มีรหัสนี้บันทึกไว้ สูตร =VLOOKUP(F3,MyData,2) จะหาเจอว่า ID ที่กรอกลงไปนั้นมีชื่อ Name กับ Amount เป็นเท่าใดอยู่เสมอ หากไม่ได้ตรวจสอบให้ดีแล้วเอาคำตอบไปใช้เลยก็อาจสอบตกโดยไม่รู้ตัว
สูตร =VLOOKUP(F3,MyData,2) ที่สร้างขึ้นนี้เป็นการสร้างแบบสั้นๆ ซึ่ง Excel กำหนดเงื่อนไขไว้ว่า ข้อมูลที่ใช้ค้นหาซึ่งอยู่ใน column ซ้ายสุดนั้น "ต้องเรียงค่าจากน้อยไปมาก" โดยจะค้นหาค่าที่มากที่สุดที่ยังน้อยกว่าหรือเท่ากับค่าที่ใช้หามาให้
ถ้าเรียงจากน้อยไปมากเอาไว้ เช่นภาพซ้ายบน และใช้ a003 ซึ่งมีบันทึกไว้ด้วย นำไปใช้ค้นหาก็จะได้ผลลัพธ์ถูกต้อง
ภาพขวาบน ถ้าใช้ a003x แม้ไม่มีค่านี้บันทึกไว้ก็ยังหาค่ามาให้ ซึ่งผิด ที่ถูกต้องก็คือต้องตอบว่า #N/A Not Available
ภาพซ้ายล่าง ต่อให้ใช้ a003 ที่มีรายการบันทึกไว้ แต่ไม่ได้เรียงจากน้อยไปมาก สูตรนี้ยังหาค่ามาให้อีกแต่กลับเป็นของรายการอื่นมาให้แทน
ภาพขวาล่าง วิธีการที่ถูกต้องของการใช้ VLookup ต้องเปลี่ยนจากสูตรแบบสั้นไปใช้สูตรแบบเต็ม =VLOOKUP(F3,MyData,2,0) หรือ =VLOOKUP(F3,MyData,2,FALSE) โดยเลข 0 หรือ False นี้ เป็นการกำหนดให้สูตรทำหน้าที่ค้นหาค่าแบบ Exact Match
แบบ Exact Match หมายความว่า ถ้าค้นหาค่าพบก็ให้ใช้รายการที่ค้นพบนั้น แต่ถ้าหาไม่พบก็ให้คืนค่าออกมาว่า Not Available หรือ #N/A ซึ่งเป็นวิธีการที่ปลอดภัยกว่า เพราะเราไม่มีโอกาสที่จะตามตรวจสอบได้เสมอไปหรอกว่ารายการยังเรียงค่าจากน้อยไปมากอยู่หรือไม่
การสร้างสูตรแบบสั้น =VLOOKUP(F3,MyData,2) นั้น Excel จะถือว่าเป็นการละส่วนขวาสุดของสูตรแบบเต็ม =VLOOKUP(F3,MyData,2,1) หรือ =VLOOKUP(F3,MyData,2,TRUE) โดยไม่ต้องใส่เลข 1 หรือ TRUE ก็ได้
ผลจากการออกแบบสูตรเพื่อช่วยให้ผู้ใช้ Excel สร้างสูตร VLookup สั้นๆได้ง่ายๆเช่นนี้ กลับกลายเป็นปัญหาใหญ่ที่ทำให้สูตรไม่เตือนเมื่อควรจะถูกเตือน (Match แบบสั้น ก็ใช้หลักเดียวกับ VLookup แบบสั้น ซึ่งเหมาะกับตารางที่เรียงค่าจากน้อยไปมากเท่านั้น)
พอถึงยุด Excel 365 ได้ปรับเปลี่ยนหลักการใช้สูตรแบบสั้นให้รัดกุมขึ้นว่า ถ้าไม่ได้กำหนดเงื่อนไขให้ครบ สูตร XLookup แบบสั้น จะค้นหาค่าแบบ exact match เสมอ
ด้วยหลักการใหม่นี้ สูตร =XLOOKUP(F3,Id,Name) แบบสั้น จึงสามารถใช้ค้นหาค่าได้ถูกต้องเสมอ ไม่ว่าตารางจะเรียงหรือไม่ และไม่ว่าจะใช้ค่าที่ไม่มีในการค้นหาค่าก็ตามโดยจะคืนค่า Not Available ออกมาให้แทน
ปล
อย่าเพิ่งรีบไปใช้ XLookup แทน VLookup นะครับ เพราะเมื่อใช้ VLookup แบบ exact match เป็นแล้วก็หมดปัญหา
สาเหตุหลักที่จะตัดสินใจเปลี่ยนไปใช้ XLookup แทนนั้น ให้ดูโครงสร้างของตารางว่าในอนาคตจะมีการแทรก column เพิ่มหรือไม่ กับต้องมั่นใจว่าเพื่อนๆทุกคนที่จะใช้แฟ้มที่มี XLookup นั้นใช้กับ Excel 365 หรือรุ่นล่าสุดกว่าเท่านั้น ส่วน VLookup ยังไงก็ตามยังยืดหยุ่นกว่าเพราะสามารถนำไปใช้กับ Excel ได้ทุก version
คลิกที่นี่เพื่ออ่านวิธีการใช้สูตร XLookup จากไมโครซอฟท์
