ทันยุคทันสมัยขึ้น เปลี่ยนจาก VLookup > XLookup > Search+Filter

สมัยที่ XLookup เปิดตัว เราฮือฮากันอยู่พักนึงว่าสูตรใหม่นี้ดีอย่างโน้นดีอย่างนี้ แนะนำให้เปลี่ยนจากสูตร VLookup ไปใช้ XLookup กัน

แม้ XLookup จะยืดหยุ่นกว่า VLookup ตรงที่ไม่ต้องอ้างอิงกับพื้นที่ตารางขนาดใหญ่ แต่ทั้ง 2 สูตรนี้มีข้อจำกัดว่าจะคืนค่าที่พบเพียงรายการแรกรายการเดียวเท่านั้น ไม่สามารถค้นหาทุกรายการที่เข้าข่ายออกมาให้เห็นทั้งหมด

ใน Excel 365 กับ Excel 2021 เป็นต้นมา แก้ปัญหานี้โดยแนะนำให้ใช้สูตร Filter แทน สามารถช่วยหาทุกรายการออกมาให้ซึ่งปลอดภัยกว่าสูตร XLookup โดยได้แนะนำสูตรใหม่นี้ไปแล้วเยอะทีเดียว สามารถค้นหาได้จากบล็อค Excel Expert Library ที่ https://excelexpertlibrary.blogspot.com/

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

ก่อนอื่นในสูตร VLookup สามารถค้นหาแค่บางส่วนของข้อความได้โดยตรง โดยใช้เครื่องหมาย * หรือ ? แทนส่วนของข้อความ เช่น ในภาพตัวอย่างนี้ ต้องการใช้ ItemChoice ที่มีคำว่า er ในการค้นหาจากชื่อ Item ก็จะสร้างสูตรแบบนี้ แต่ได้คำตอบเพียงค่าเดียว ไม่สามารถหาทั้งรายการออกมาให้

=VLOOKUP( "*"&ITEMChoice&"*", E3:G26, 2, 0)

++++++++++++++++++++++++++++++ 

ส่วนสูตร XLookup ใช้สูตรตามนี้ สามารถหาทั้งรายการออกมาให้ แต่ได้เพียงรายการแรกที่เจอเท่านั้น

=XLOOKUP(1, --ISNUMBER( SEARCH(ITEMChoice,ITEM)), MyData)

ในการค้นด้วยสูตร XLookup ใช้เลข 1 เป็นเงื่อนไขในการค้นหา โดยหาจากพื้นที่ของ Item ที่มีคำว่า er ประกอบอยู่ข้างใน โดยใช้สูตร Search ช่วย

SEARCH(ITEMChoice,ITEM) พอเจอว่ามีคำว่า er จะคืนค่าออกมาเป็นตัวเลขว่าอยู่ที่อักษรลำดับใด

ISNUMBER( SEARCH(ITEMChoice,ITEM)) ใช้เปลี่ยนตัวเลขที่เจอให้เป็นค่า True แทน

--ISNUMBER( SEARCH(ITEMChoice,ITEM)) ใส่ -- ลงไปข้างหน้าเพื่อเปลี่ยน True ให้เป็นเลข 1 

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

++++++++++++++++++++++++++++++

ในการใช้สูตร Filter ให้นำส่วนของ ISNUMBER( SEARCH(ITEMChoice,ITEM)) มาใช้เป็นเงื่อนไขในการค้นหา

=FILTER( MyData, ISNUMBER( SEARCH(ITEMChoice,ITEM) ) )

สูตรนี้จะหาทุกรายการที่มีคำว่า er แทรกอยู่ในชื่อ Item

Download ตัวอย่างได้จาก

https://drive.google.com/file/d/16Drg35ime66Cm-R0lU7K3dgMgGiA7wiu/view?usp=sharing 

*****************************************

ประโยชน์ของการใช้ Search + Filter ที่สำคัญมาก คือ เราไม่ต้อง cleaning อีกต่อไปครับ ไม่ต้องเสียเวลาไปแก้ไขข้อมูลด้วย Power Query ซึ่งว่าไปแล้วไม่มีทางรู้ได้เลยว่าข้อมูลที่นำเข้ามาเพี้ยนต่างจากเดิมอะไรอีกบ้าง

ให้นำข้อมูลเข้ามาตามเดิมที่รับมาแล้วใช้สูตรค้นหาตามนี้แหละครับ จบ.  

Copilot :

พูดได้เต็มปากเลยครับว่าแนวคิดนี้คือการ “ปลดล็อกความยุ่งยากของการเตรียมข้อมูล” ด้วยพลังของสูตรแบบไดนามิกใน Excel 365 🎯

---

🔓 จุดเปลี่ยนสำคัญ: จาก “Clean ก่อนใช้” → “ใช้เลยแล้วคัดกรอง”

✅ ประโยชน์หลักของการใช้ SEARCH + FILTER

ประโยชน์	อธิบาย
ไม่ต้อง Clean ข้อมูลก่อน	ไม่ต้องใช้ Power Query หรือแก้ไขข้อมูลต้นทางให้เป๊ะ
รองรับข้อมูลเพี้ยนหรือไม่สมบูรณ์	ค้นหาด้วยบางส่วนของข้อความ เช่น "er" หรือ "ing" ได้
ลดเวลาเตรียมข้อมูล	ไม่ต้องสร้างเงื่อนไขหลายชั้นหรือแก้ไข formatting
ค้นหาหลายรายการพร้อมกัน	ได้ผลลัพธ์ทุกแถวที่ตรงเงื่อนไข ไม่ใช่แค่แถวแรก
ปลอดภัยต่อการเปลี่ยนแปลงข้อมูลต้นทาง	ถ้าข้อมูลเปลี่ยน สูตรยังทำงานได้ ไม่ต้องแก้สูตรใหม่
เหมาะกับการสอนและสร้าง dashboard แบบ Exception-based	ค้นหาสิ่งที่ “ควรสนใจ” โดยไม่ต้องกรองข้อมูลก่อน

---

🧠 Metaphor สำหรับการสอน

“สูตร SEARCH + FILTER เหมือนแว่นตาอัจฉริยะที่มองทะลุข้อมูลเพี้ยนได้ โดยไม่ต้องล้างเลนส์ก่อน”

หรืออีกแบบ:

“Power Query คือการล้างจานก่อนกิน ส่วน SEARCH + FILTER คือการใช้ตะเกียบเลือกของที่กินได้เลยจากจานรวม”

---

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

=FILTER(MyData, ISNUMBER(SEARCH("er", ITEM)))

หรือถ้าใช้หลายคำ:

=FILTER(MyData, BYROW(ITEM, LAMBDA(row, SUM(--ISNUMBER(SEARCH({"er","ing"}, row)))>0)))

---