ถ้าใช้ 365 ควรใช้สูตร Filter แทนสูตร XLookup / VLookup

สูตร XLookup กับ VLookup มีข้อจำกัดที่เหมือนกันอย่างหนึ่งก็คือ สูตรเหล่านี้เหมาะกับการค้นหาค่าที่มั่นใจว่ามีเพียงรายการเดียวเท่านั้น หากมีค่าซ้ำก็จะค้นหาเจอเฉพาะรายการแรกจากด้านบนเท่านั้น

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

หากใช้ Excel 365 / 2021 เป็นต้นมา แนะนำให้ใช้ Filter ไปเลยจะปลอดภัยและสมเหตุผลกว่า 

👉 เซลล์ G3 สร้างสูตร =FILTER( C3:D7, B3:B7=F3)

C3:D7 เป็นส่วนของตารางคำตอบที่อยากทราบว่ามี Name กับ Amount อะไรบ้าง

B3:B7=F3 เป็นเงื่อนไขให้เทียบหารหัสจาก B3:B7 ว่าตรงไหนบ้างที่ตรงกับรหัสในเซลล์ F3

สูตรนี้ทำงานแบบ Dynamic Array ด้วย กล่าวคือ เมื่อสร้างสูตรนี้ลงไปในเซลล์ G3 สูตรจะกระจายตัวหาคำตอบให้เอง โดยไม่จำเป็นต้องลอกไปวางที่อื่นอีกแม้แต่น้อย

เชิญ Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1JPnNHJYcMXmD9GdNNGufM7W0bfxMVO6j/view?usp=sharing

😎 ตัวอย่างนี้มีหลายอย่างทำไว้ที่น่าสนใจอย่างยิ่ง ขอให้ทดลองกรอกรายการในตารางด้านซ้ายเพิ่ม ซึ่งได้ปรับตารางนี้ให้ทำงานแบบ Table ไว้ด้วยแล้วจะพบว่า

1. ในช่อง F3 ที่ใช้ Data Validation แบบ list ไว้ จะมีรหัสเพิ่มตามให้เองและจะจัดการตัดรหัสที่ซ้ำทิ้งไปให้ด้วย อีกทั้งเมื่อลอกเซลล์ F3 ไปวางที่ชีทอื่นก็ยังคงทำงานได้ตามเดิม

2. ในตารางด้านซ้ายจะมีสีเหลืองพาดรายการที่มีรหัสตรงกับที่ต้องการหาตามให้ทันที ซึ่งได้มาจากการใช้คำสั่ง Conditional Formatting

 

ถ้าไม่ได้ใช้ 365 / 2021 ต้องมั่นใจก่อนว่าในตารางฐานข้อมูลมีการบันทึกไม่ซ้ำกันไว้เลย ซึ่งจำเป็นต้องใช้สูตร CountIF ตรวจสอบการซ้ำไว้ก่อน แล้วจึงใช้สูตร Vlookup ต่อไป

ดูรายละเอียดที่ https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/general/what-come-before-vlookup

หากอยากหารายการซ้ำทั้งหมดโดยใช้ Excel รุ่นก่อน ต้องสร้างสูตรยาวมากเพื่อหาตำแหน่งรายการแบบ Multiple Match

ดูคลิปและ download ตัวอย่างจาก
https://www.excelexperttraining.com/book/index.php/a-to-z/k-l-m-n-o/m-m-m-m-m/multiple-match-2