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)

ลองเดินตามสูตรจริงในชีท “ใบสั่งขาย” กันทีละขั้น:

1. ดูแถวที่ 2 รหัส P001 — เซลล์ F2 ดึงชื่อ “เสื้อยืดคอกลม” จากตารางราคาสินค้า
2. เซลล์ G2 ดึงราคา 120 บาท
3. เซลล์ H2 คูณ จำนวน x ราคาได้ 1,200 บาท
4. ลากสูตรลงมา 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 — คู่หูที่ยืดหยุ่นยิ่งกว่าเดิมและถูกใช้เป็นหัวใจของสูตรขั้นสูงหลายแบบ เจอกันครับ! 😊