เคล็ดช่วยจำ โครงสร้างสูตร XLookup

🤩 เคล็ดช่วยจำ โครงสร้างสูตร XLookup

ถ้าดูตามตำราแล้วเอาแต่ท่องจำตามตำรา โครงสร้างสูตร XLookup เป็นตามนี้

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

สูตร XLookup มีโครงสร้างสูตรยาวกว่า VLookup ขึ้นเยอะ เห็นตอนแรกจะงงทีเดียวว่าส่วนไหนในวงเล็บใช้ทำอะไร แทนที่จะเอาแต่ท่องจำ มาลองคิดแบบคนสร้างสูตรกันดีกว่า จะได้เข้าใจว่าที่มาที่ไปของแต่ละส่วนมาได้เพราะเหตุผลอะไร (How XLookup was built)

👉 เวลาเราจะหาอะไร ต้องรู้ก่อนใช่ไหมว่าสิ่งที่จะหานั้นเป็นอะไร ด้วยเหตุนี้ในวงเล็บของสูตรส่วนแรกจึงต้องเป็นค่าที่ใช้หา lookup_value เป็นตำแหน่งเซลล์ F3 ซึ่งบันทึก a003x เอาไว้ตามภาพ

=XLOOKUP(lookup_value,
=XLOOKUP(F3,

👉 จากนั้นต้องถามต่อไปว่าจะให้หาจากพื้นที่ตรงไหน ดังนั้นส่วนที่สองในวงเล็บ lookup_array จึงหมายถึงพื้นที่ ID ซึ่งเก็บค่าหรือรหัสทั้งหมดเอาไว้ ซึ่งสูตรนี้กำหนดไว้ว่าต้องหาจากตารางตามแนวตั้งแนวเดียวหรือแนวนอนแนวเดียวเท่านั้น ห้ามใช้หลาย column/row

=XLOOKUP(lookup_value, lookup_array,
=XLOOKUP(F3, ID

👉 พอสูตรนี้เอาสองส่วนแรกไปค้นหาค่าเจอแล้วว่าอยู่ตรงไหน ก็จะเอาตำแหน่งนั้นไปเทียบกับตารางคำตอบ Name กับ Amount ซึ่งเรียกว่า return array เป็นส่วนที่สาม ซึ่งต้องมีขนาดเท่ากันกับ lookup array จะได้หาค่าตรงตำแหน่งลำดับที่ตรงกันออกมาให้

=XLOOKUP(lookup_value, lookup_array, return_array,
=XLOOKUP(F3, ID, Name
=XLOOKUP(F3, ID, Amount

👍 ปกติใช้แค่ 3 ส่วนนี้ในวงเล็บก็พอแล้ว แต่ถ้าอยากจะทำให้คืนค่าแปลกๆพิเศษกว่าที่สูตรเดิมๆเคยทำได้ก็ใส่เพิ่มลงไปตามสะดวก

ถัดจากนี้เป็นส่วนที่จะเลือกใส่หรือไม่ก็ได้ จะเห็นว่าในโครงสร้างใส่ [ ] คร่อมเอาไว้เพื่อแสดงว่าส่วนนี้เป็น optional

👉 ส่วนที่สี่ต่อไป [if_not_found] เป็นส่วนพิเศษหน่อย ทำไว้เผื่อว่าถ้าหาค่าไม่พบจะให้แทนค่าด้วยคำว่าอะไรหรือใช้ค่าอะไรแทน ถ้าไม่ใส่ก็จะคืนค่า #N/A ออกมาให้

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
=XLOOKUP(F3, ID, Name, "No more"
=XLOOKUP(F3, ID, Amount, 0

สาเหตุที่ใช้คำว่า No more เพราะค่าเดิมที่ควรจะหาได้นั้นเป็นตัวอักษร a b c d จึงใช้คำแทนที่เป็นตัวอักษรเหมือนกัน ส่วน Amount มีค่าเป็นตัวเลข จึงให้แทนด้วยเลข 0 ซึ่งเป็นตัวเลขแทนที่ตัวเลขที่ควรจะหาค่ามาให้

👉 ส่วนที่ห้าเรียกว่า match_mode เพื่อกำหนดวิธีการค้นหาค่าที่ต้องการออกมาให้ โดยเลือกใช้เลข -1, 0, 1 ซึ่งถ้าละไปไม่ใส่ก็จะถือว่าใช้ 0 แทน

สาเหตุที่มีส่วนที่ห้านี้ขึ้นมา เพราะหลังจากที่มีการเตือน error เมื่อค้นไม่พบแล้ว หากต้องการหาค่าอื่นแทนขึ้นมาล่ะจะได้สามารถนำค่าที่น้อยกว่าหรือมากกว่ามาใช้ได้ด้วย โดยส่วนนี้นอกจากเลข -1, 0, 1 แล้วยังมีเลข 2 มาให้ใช้ค้นหาค่าโดยใช้ wild card * กับ ? ช่วยในการค้นหาจากบางส่วนของข้อความได้อีก

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],

วิธีจำในส่วนนี้ให้คิดถึงเส้นจำนวนที่เรียงจากเลข -1 ไป 0 ไปเลข 1 ว่า

👈 ถ้าใส่เลข -1 จะไปหาค่าที่น้อยกว่าค่าที่ใช้หา ซึ่ง a003 น้อยกว่า a003x
=XLOOKUP(F3, ID, Name, "No more", -1
=XLOOKUP(F3, ID, Amount, 0, -1

👍 ถ้าใส่เลข 0 จะไปหาค่าที่ตรงกันกับค่าที่ใช้หา ถ้าเจอก็ใช้ค่านั้น ถ้าไม่เจอก็ error แทนออกมา
=XLOOKUP(F3, ID, Name, "No more", 0
=XLOOKUP(F3, ID, Amount, 0, 0

👉 ถ้าใส่เลข 1 จะไปหาค่าที่มากกว่าค่าที่ใช้หา ซึ่ง a004 มากกว่า a003x
=XLOOKUP(F3, ID, Name, "No more", 1
=XLOOKUP(F3, ID, Amount, 0, 1

(หลักการเทียบค่าส่วนนี้ต่างจากที่เคยใช้ใน VLookup ซึ่งถือว่า 0 เป็นการหาแบบ Exact Match ตารางจะเรียงหรือไม่ก็ได้ แต่ถ้าใช้เลข 1 จะค้นหาแบบ Approaching match หรือ Approximate match และตารางต้องเรียงค่าจากน้อยไปมากด้วย ส่วนใน XLookup จะใช้การเทียบกับค่าที่มีอยู่โดยไม่ได้มองการเรียงในตาราง)

👉 ส่วนที่หก ซึ่งเป็นส่วนสุดท้ายนั้น มีไว้เผื่ออยากจะหาจากล่างขึ้นบน จำไว้ว่าใช้แบบพิเศษก็แล้วกัน ถ้าใส่เลข 1 หรือ 2 ที่เป็นค่าบวกก็จะหาแบบธรรมชาติบนลงล่าง แต่พอใส่เครื่องหมายลบด้านหน้าเป็น -1 หรือ -2 ก็จะหาแบบผิดธรรมชาติ จากล่างขึ้นบนแทน ซึ่งจากภาพนี้ละไว้ไม่ใส่เลขจึงถือว่าหาแบบเลข 1 ตามธรรมชาติ

ส่วนเลข 2 ก็ตรงกับคำว่า Bi ที่แปลว่า 2 Binary

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP(F3, ID, Name, "No more", 1,)
=XLOOKUP(F3, ID, Amount, 0, 1,)

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

ตอนนี้ขอให้ทราบไว้แค่นี้ก่อน มากไปจะงงแล้วพาลทำให้ไม่อยากจำหรือจำยากเสียอีก สูตรรุ่นใหม่ใน Excel 365 มีคุณสมบัติพิเศษอื่นๆอีกที่เหนือกว่าสูตรรุ่นก่อนๆ คอยติดตามตอนต่อๆๆๆๆไปครับ

เปิดตำราของไมโครซอฟท์ได้จาก
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?WT.mc_id=M365-MVP-4000499