VLOOKUP + XLOOKUP — ค้นหาแบบมือโปร ดูได้ทั้งซ้ายและขวา
1. จาก VLOOKUP เบื้องต้น สู่สงครามค้นหาข้อมูลขั้นสูง
ถ้าคุณอ่านบทความ VLOOKUP ฉบับพื้นฐานมาแล้ว ผมเชื่อว่าคุณน่าจะร้องอ๋อเลยว่าการค้นหาข้อมูลข้ามตารางมันช่วยประหยัดเวลาไปขนาดไหน — แค่ 4 อาร์กิวเมนต์ ก็ลากข้อมูลจากตารางอ้างอิงมาวางได้ทั้งชีต
แต่วันนี้เราจะไม่หยุดแค่นั้นครับ!
เพราะ VLOOKUP แม้จะใช้งานง่าย แต่ก็มีข้อจำกัดหลายจุดที่คนทำงานตัวจริงต้องเจอจนปวดหัว เช่น ดูได้เฉพาะคอลัมน์ที่อยู่ขวา, หาค่าแบบตรงเป๊ะไม่ได้ถ้าลืมใส่ FALSE, หรือดึงค่าจากคอลัมน์ที่ถูกแทรกเพิ่มจนเลข index เลื่อน
โชคดีที่ยุคนี้เรามี XLOOKUP มาแทนที่ — ฟังก์ชันค้นหาที่ตอบโจทย์ทุกปัญหาและทำให้ VLOOKUP ดูเชยไปเลยในพริบตา
เปิดไฟล์ตัวอย่าง แล้วทำตามไปด้วยกันนะครับ — ผมเตรียมข้อมูลร้านขายเสื้อผ้าออนไลน์ไว้ให้ลองเล่นแล้ว
2. ทบทวน VLOOKUP สั้นๆ — สงครามครึ่งเดียว
VLOOKUP คือการค้นหาจากซ้ายไปขวา โดยโครงสร้างคือ:
=VLOOKUP( ค่าที่ค้นหา , ตารางอ้างอิง , ลำดับคอลัมน์ , FALSE )ในไฟล์ตัวอย่างชีท “ใบสั่งขาย” ผมใช้ VLOOKUP ดึงชื่อสินค้าและราคาจากชีท “ราคาสินค้า” มาใส่โดยเทียบจากรหัสสินค้า:
| คอลัมน์ | สูตร |
|---|---|
| C2 ชื่อสินค้า (VLOOKUP) | =VLOOKUP($A2,ราคาสินค้า!$A$2:$C$6,2,FALSE) |
| D2 ราคา (VLOOKUP) | =VLOOKUP($A2,ราคาสินค้า!$A$2:$C$6,3,FALSE) |
| E2 รวมเงิน | =B2*D2 |
ลองสังเกตดูว่า VLOOKUP ทำงานได้สมบูรณ์จริง — แต่พอใช้ไปเรื่อยๆ คุณจะสะดุดกับข้อจำกัดเหล่านี้:
| ข้อจำกัดของ VLOOKUP | ผลกระทบ |
|---|---|
| ดูได้เฉพาะคอลัมน์ที่อยู่ขวาของค่าค้นหา | หาแบบย้อนกลับไม่ได้ — ต้องจัดคอลัมน์ใหม่ทุกครั้ง |
| ใช้เลข index ของคอลัมน์ | ถ้าแทรกคอลัมน์ใหม่ ค่าที่ดึงมาจะเพี้ยนทันที |
| ลืมใส่ FALSE | จะได้ค่าประมาณแทนค่าแบบตรงเป๊ะ — เงียบ! |
| ค่าไม่เจอ | คืน #N/A ซึ่งหน้าตาน่าเกลียด |
นี่คือเหตุผลว่าทำไมผมถึงอยากให้คุณรู้จัก XLOOKUP มากๆ ครับ
จริงๆ แล้วข้อจำกัดเหล่านี้ไม่ใช่เพดานของ Excel แต่มันคือข้อจำกัดของ VLOOKUP ที่ถูกออกแบบมานานแล้ว — พอข้อมูลจริงในที่ทำงานซับซ้อนขึ้น ทั้งการแทรกคอลัมน์ การสลับลำดับข้อมูล หรือการค้นหาย้อนกลับ จึงเกิดสูตรพังหรือค่าผิดเพี้ยนกันอยู่เรื่อยๆ เมื่อรู้จัก XLOOKUP แล้ว คุณจะมองเห็นภาพว่าการเขียนสูตรค้นหาสมัยใหม่ควรเป็นยังไง
💡 เคล็ดลับ: ใช้ FALSE (หรือ 0) ทุกครั้งเมื่อต้องการค่าตรงเป๊ะ เช่น การค้นหารหัสสินค้า — อย่าใช้ TRUE ในงานแบบนี้เด็ดขาด เพราะมันทำงานแบบประมาณค่าซึ่งให้ผลผิด
3. รู้จัก XLOOKUP — ฟังก์ชันค้นหาที่มาแรงที่สุดในยุคนี้
XLOOKUP เป็นฟังก์ชันค้นหาที่ออกแบบมาแทน VLOOKUP และ HLOOKUP ใน Microsoft 365 / Excel 2021 ขึ้นไป โครงสร้างง่ายกว่ามากเพราะแยก “คอลัมน์ที่ค้น” กับ “คอลัมน์ที่ดึงค่า” ออกจากกันชัดเจน:
=XLOOKUP( ค่าที่ค้นหา , ช่วงที่ค้นหา , ช่วงที่จะดึงค่า , [ค่าเมื่อไม่เจอ] , [match_mode] , [search_mode] )มาดูการใช้งานจริงในชีท “ใบสั่งขาย” ครับ — ผมเพิ่มคอลัมน์ F-H ไว้เทียบเคียงกับ VLOOKUP:
| คอลัมน์ | สูตร |
|---|---|
| F2 ชื่อสินค้า (XLOOKUP) | =XLOOKUP($A2,ราคาสินค้า!$A$2:$A$6,ราคาสินค้า!$B$2:$B$6) |
| G2 ราคา (XLOOKUP) | =XLOOKUP($A2,ราคาสินค้า!$A$2:$A$6,ราคาสินค้า!$C$2:$C$6) |
| H2 รวมเงิน (XLOOKUP) | =B2*G2 |
ลองดู F2 เทียบกับ C2 — ผลลัพธ์เหมือนกันเป๊ะ! แต่จุดที่ต่างคือ XLOOKUP แยกช่วงค้นหา (คอลัมน์ A ของราคาสินค้า) กับช่วงที่ดึงค่า (คอลัมน์ B ของราคาสินค้า) แยกออกจากกัน ทำให้เวลาแทรกคอลัมน์หรือสลับข้อมูล สูตรไม่เพี้ยนอีกเลย
3.1 Reverse Lookup — ดูได้ทั้งซ้ายและขวา
จุดเด่นที่ว้าวที่สุดของ XLOOKUP คือมันดึงค่าจากคอลัมน์ฝั่งซ้ายของค่าค้นหาได้ด้วย! เปิดชีท “XLOOKUP ตัวอย่าง” ดูครับ — ผมให้ค้นหาชื่อสินค้า แล้วเอารหัสสินค้าฝั่งซ้ายออกมาให้:
| ชื่อสินค้า | รหัสสินค้า |
|---|---|
| รองเท้าผ้าใบ | =XLOOKUP(A3,ราคาสินค้า!$B$2:$B$6,ราคาสินค้า!$A$2:$A$6) |
| กระเป๋าเป้ | =XLOOKUP(A4,ราคาสินค้า!$B$2:$B$6,ราคาสินค้า!$A$2:$A$6) |
ถ้าใช้ VLOOKUP แบบนี้คงต้องไปย้ายคอลัมน์ให้รหัสไปอยู่ฝั่งซ้ายก่อน — แต่ XLOOKUP ทำได้เลยโดยไม่ต้องแตะโครงตาราง
3.2 ไล่ดูแต่ละอาร์กิวเมนต์ให้ชัด
หลายคนเห็น XLOOKUP มี 6 ตำแหน่งแล้วกลัว แต่จริงๆ ใช้แค่ 3 ตำแหน่งแรกก็ทำงานได้แล้ว ที่เหลือเป็น option ล้วนๆ การ์ดไว้ได้เลย มาดูกันทีละตัว:
| อาร์กิวเมนต์ | ความหมาย | จำเป็นไหม |
|---|---|---|
| lookup_value | ค่าที่ต้องการค้นหา | จำเป็น |
| lookup_array | ช่วงที่เอาไว้ค้นหา | จำเป็น |
| return_array | ช่วงที่อยากดึงค่ามาแสดง | จำเป็น |
| if_not_found | ข้อความเมื่อไม่เจอค่า | ไม่จำเป็น (คืน #N/A ถ้าเว้น) |
| match_mode | 0=ตรงเป๊ะ, -1=ค่าน้อยสุดใกล้เคียง, 1=ค่ามากสุดใกล้เคียง, 2=wildcard | ไม่จำเป็น (default 0) |
| search_mode | 1=จากบนลงล่าง, -1=จากล่างขึ้นบน, 2/3=binary search | ไม่จำเป็น (default 1) |
สังเกตว่า default ของ match_mode คือ 0 (ตรงเป๊ะ) — ต่างจาก VLOOKUP ที่ default เป็นแบบประมาณค่า ซึ่งเป็นต้นเหตุของความผิดพลาดเงียบๆ ที่หลายคนเจอ
3.3 ค้นหาจากท้ายตารางด้วย search_mode = -1
ในทางบัญชีหรือสต็อก เรามักเจอข้อมูลที่มีรหัสเดิมซ้ำหลายบรรทัด เช่น บันทึกยอดขายรายวันของรหัส P001 หลายครั้ง แล้วอยากดึงยอดล่าสุด (บรรทัดสุดท้าย) ของรหัสนั้น:
=XLOOKUP("P001", ราคาสินค้า!$A$2:$A$6, ราคาสินค้า!$C$2:$C$6, "ไม่พบ", 0, -1)ตรง search_mode ใส่ -1 แปลว่าให้เริ่มค้นจากท้ายช่วงขึ้นมา — ได้ค่าล่าสุดแทนค่าแรกเสมอ เหมาะกับงานที่ข้อมูลถูก append ต่อท้ายเรื่อยๆ
💡 เคล็ดลับ: จำง่ายๆ — search_mode 1 (default) เจอค่าแรกสุด, -1 หาค่าที่อยู่ท้ายสุด ใช้เมื่อต้องการ “ค่าล่าสุด” ของชุดข้อมูลที่ซ้ำกัน
4. XLOOKUP กับค่าไม่เจอ — หมดยุค #N/A เต็มชีต
จำได้ไหมว่าตอน VLOOKUP หาค่าไม่เจอมันคาย #N/A ออกมาให้ดูไม่สวยแค่ไหน? XLOOKUP มีอาร์กิวเมนต์ที่ 4 คือ if_not_found ไว้กำหนดข้อความที่อยากให้แสดงเมื่อไม่เจอ
ดูแถวที่ 6 ในชีท “XLOOKUP ตัวอย่าง” ครับ — ผมพิมพ์ชื่อ “แว่นกันแดด” ที่ไม่มีในตารางราคาสินค้า:
=XLOOKUP(A6,ราคาสินค้า!$B$2:$B$6,ราคาสินค้า!$A$2:$A$6,"ไม่พบสินค้า")ผลลัพธ์ออกมาเป็นคำว่า “ไม่พบสินค้า” แทนที่จะเป็น #N/A — เหมาะมากสำหรับงานที่ต้องส่งรายงานต่อ หรือต้องแจ้งเพื่อนร่วมทีมว่าสินค้ายังไม่ได้อัปราคา
⚠️ ข้อควรระวัง: การใช้ if_not_found อย่าลืมใช้เครื่องหมายคำพูดครอบข้อความนั้นเสมอครับ ไม่งั้น Excel จะมองว่าเป็นชื่อช่วงแล้ว Error
5. ค้นหาแบบ Wildcard — หาแค่บางส่วนก็เจอ
XLOOKUP รองรับการค้นหาแบบ Wildcard ด้วยการใส่ match_mode เป็น 2 — ใช้สัญลักษณ์ * (ตัวอะไรก็ได้) หรือ ? (ตัวเดียว) ช่วยค้นหาคร่าวๆ ได้
ในชีท “XLOOKUP ตัวอย่าง” แถวที่ 10 ผมทดสอบค้นหาคำว่า “เสื้อ*” เพื่อให้ได้สินค้าที่ขึ้นต้นด้วย “เสื้อ”:
=XLOOKUP(A10,ราคาสินค้า!$B$2:$B$6,ราคาสินค้า!$B$2:$B$6,"ไม่พบ",2)ผลลัพธ์ที่ได้คือ “เสื้อยืดคอกลม” — เหมาะกับตอนที่คุณจำชื่อสินค้าได้แค่บางส่วน แต่ต้องระวังว่าในตารางนี้ค่าแรกที่ตรงกับ pattern จะถูกเลือกก่อน
6. XLOOKUP + IFERROR — กันเพลี้ยอีกรอบ
แม้ XLOOKUP จะมี if_not_found ในตัว แต่บางสถานการณ์คุณยังอยากใช้ IFERROR ซ้อนอีกชั้นเพื่อรวมศูนย์จัดการ error ทั้งหลาย เช่น เมื่อสูตรอื่นปล่อย ERROR ออกมา:
=IFERROR(XLOOKUP(A2,ราคาสินค้า!$A$2:$A$6,ราคาสินค้า!$C$2:$C$6),"ติดต่อฝ่ายขาย")แบบนี้ถ้าค่าผิดพลาดอะไรก็ตาม (หาไม่เจอ, เซลล์อ้างอิงว่าง, ประเภทข้อมูลผิด) ระบบจะคืนข้อความ “ติดต่อฝ่ายขาย” แทนที่จะเป็น error วนๆ ครับ
วิธีนี้ช่วยให้รายงานที่ต้องส่งต่อดูสะอาดและเป็นมืออาชีพ — เพราะคนที่เปิดไฟล์จะเห็นคำอธิบายที่อ่านเข้าใจ แทนที่จะเห็น #N/A หรือ #VALUE! แล้วต้องมานั่งเดาว่ามันผิดตรงไหน เก็บเทคนิคนี้ไว้ใช้กับทุกสูตรค้นหาเลยครับ
💡 เคล็ดลับ: ใช้ XLOOKUP ตอนที่ใช้ IFERROR — if_not_found ครอบ error ที่เกิดจากการค้นหาไม่ได้ทั้งหมด เช่น ถ้าช่วงอ้างอิงยาวไม่เท่ากัน จะยัง Error อยู่ดี IFERROR จึงเป็นเกราะเสริมชั้นสุดท้าย
7. เมื่อไหร่ควรใช้ XLOOKUP vs VLOOKUP
สรุปให้เห็นภาพชัดๆ ครับ:
| สถานการณ์ | แนะนำให้ใช้ |
|---|---|
| มี Microsoft 365 / Excel 2021 ขึ้นไป | XLOOKUP (ใหม่กว่า รองรับทุกฟีเจอร์) |
| ไฟล์ต้องเปิดกับ Excel เวอร์ชันเก่า | VLOOKUP (เข้ากันได้ทุกเวอร์ชัน) |
| ต้องค้นหาแบบย้อนกลับ (ค่าอยู่ขวา ผลอยู่ซ้าย) | XLOOKUP นั่นแหละ |
| ต้องการกำหนดข้อความเมื่อไม่เจอ | XLOOKUP (if_not_found) |
| ทำงานร่วมกับ Table / Named Range | ใช้ได้ทั้งคู่ แต่ XLOOKUP ยืดหยุ่นกว่า |
📌 ข้อควรจำ: เวลาเขียนสูตรร่วมกับ Excel Table คุณใช้ชื่อคอลัมน์ เช่น
สินค้า[รหัส]แทนช่วง $A$2:$A$6 ได้ — ทั้ง VLOOKUP และ XLOOKUP รองรับครับ ช่วยให้สูตรอ่านง่ายขึ้นเยอะ
8. ตัวอย่างประยุกต์จากงานจริง
สถานการณ์ที่ 1: ร้านค้าออนไลน์คำนวณยอดตามรหัสสินค้า
อย่างในไฟล์ตัวอย่าง — มีใบสั่งขาย 5 รายการ แต่ละรายการมีรหัสสินค้า ใช้ XLOOKUP ดึงชื่อและราคามา แล้วคูณจำนวนเป็นยอดรวมแต่ละแถว แล้ว SUM เอาแถวรวม สรุปยอดขายทั้งบิลได้ในทันที แถวสุดท้ายคอลัมน์ H รวมยอดด้วย SUM(H2:H6)
ลองเดินตามสูตรจริงในชีท “ใบสั่งขาย” กันทีละขั้น:
- ดูแถวที่ 2 รหัส P001 — เซลล์ F2 ดึงชื่อ “เสื้อยืดคอกลม” จากตารางราคาสินค้า
- เซลล์ G2 ดึงราคา 120 บาท
- เซลล์ H2 คูณ จำนวน x ราคาได้ 1,200 บาท
- ลากสูตรลงมา 5 แถว แล้วดูแถว “รวม” ที่ H7 — SUM รวมทั้งบิลเป็นยอดเดียว
ถ้าอยากเห็นว่า VLOOKUP กับ XLOOKUP ให้ผลเหมือนกันไหม ให้เทียบคอลัมน์ C (VLOOKUP) กับ F (XLOOKUP) ดู — เหมือนกันเป๊ะ แต่ XLOOKUP เขียนง่ายและไม่พังเพราะเลข index
สถานการณ์ที่ 2: ซัพพลายเชนเช็คราคาวัตถุดิบ
ฝ่ายซื้อมีตารางวัตถุดิบที่เรียงตามชื่อ (คอลัมน์แรกคือชื่อ) แต่ต้องการออกรายงานตามรหัส FO-xxx — ใช้ XLOOKUP ย้อนกลับจากชื่อไปหารหัส หรือจากรหัสไปหาราคาได้โดยไม่ต้องย้ายคอลัมน์ใด เช่นเดียวกับชีท “XLOOKUP ตัวอย่าง” ที่ค้นหาชื่อแล้วได้รหัสออกมา
สถานการณ์ที่ 3: HR เช็คพนักงานที่ย้ายแผนก
ฝ่ายบุคคลมีตารางพนักงานสองชุด — ชุดเก่าเรียงตามชื่อ ชุดใหม่เรียงตามรหัสพนักงาน สมมติต้องการตรวจสอบว่าพนักงานที่ย้ายแผนกมีอัตราเงินเดือนใหม่หรือยัง เมื่อเงินเดือนที่ดึงมาเป็น 0 ก็จะได้รู้ว่ายังไม่ได้อัปเดต — เหมาะกับ if_not_found ที่ให้ผลลัพธ์เป็นข้อความชัดเจน แทนที่จะเงียบเพราะ look ออกมาเป็นค่าว่าง
9. ข้อสังเกตและปัญหาเจอบ่อย
แม้ XLOOKUP จะเจ๋ง แต่ก็มีจุดที่ควรรู้ไว้ก่อนใช้:
| อาการ | สาเหตุ | วิธีแก้ |
|---|---|---|
| คืน #VALUE! | ช่วงค้นหา กับ ช่วงดึงค่า ยาวไม่เท่ากัน | ปรับให้เป็นช่วงที่มีจำนวนแถวเท่ากัน |
| คืน #NAME? | ใช้ Excel เวอร์ชันเก่าที่ไม่มี XLOOKUP | ใช้ VLOOKUP แทน หรืออัปเดตเป็น 365 |
| ได้ค่าแรกตลอดแม้มีค่าซ้ำ | default search จากบนลงล่าง | ใส่ search_mode = -1 เพื่อหาค่าล่าสุด |
| ค้นหาไม่ได้ทั้งที่มีข้อมูล | ประเภทข้อมูลไม่ตรง (ตัวเลข vs ข้อความ) | เช็คด้วย TYPE หรือใช้ TRIM/จัด format |
| ได้ค่าไม่ถูกต้องแบบเงียบ | ลืมให้ match_mode เป็น 0 (ตรงเป๊ะ) | ระบุ match_mode = 0 เสมอสำหรับรหัส |
⚠️ ข้อควรระวัง: ถ้าได้ค่าผิดโดยไม่มี error แสดงให้เห็น ให้เช็คก่อนว่า match_mode เป็น 0 หรือไม่ — เพราะ default ของ VLOOKUP อาจทำ silent error ที่ตรวจยากมาก
10. สรุป
XLOOKUP เป็นฟังก์ชันที่ผมแนะนำให้อัปเกรดใช้แทน VLOOKUP ได้เลยถ้าคุณใช้ Microsoft 365 — เพราะมัน:
| ฟีเจอร์ | ประโยชน์ |
|---|---|
| แยกช่วงค้นหาและช่วงดึงค่า | สูตรไม่เพี้ยนเมื่อแทรกคอลัมน์ |
| Reverse Lookup | ดึงค่าฝั่งซ้ายได้ ไม่ต้องย้ายตาราง |
| if_not_found | กำหนดข้อความกัน #N/A ให้สวยงาม |
| Wildcard (match_mode=2) | ค้นหาแบบบางส่วนได้ |
| ใช้ร่วมกับ IFERROR ได้ | จัดการ error ทุกชนิดเป็นระบบ |
เปิดไฟล์ตัวอย่างแล้วลองเทียบ VLOOKUP กับ XLOOKUP ในชีท “ใบสั่งขาย” ดูครับ — ผลลัพธ์เหมือนกันแต่โครงสร้างสูตรโคตรยืดหยุ่น จากนั้นไปเล่นชีท “XLOOKUP ตัวอย่าง” เพื่อลอง reverse lookup, if_not_found และ wildcard ด้วยตัวเอง
บทความหน้าผมจะพาคุณไปรู้จัก INDEX + MATCH — คู่หูที่ยืดหยุ่นยิ่งกว่าเดิมและถูกใช้เป็นหัวใจของสูตรขั้นสูงหลายแบบ เจอกันครับ! 😊