หากต้องเลือกระหว่าง XLOOKUP กับ VLOOKUP ให้ ใช้ XLOOKUP ถ้าทุกคนที่เปิดไฟล์ใช้ Excel for Microsoft 365, Excel 2021 ขึ้นไป หรือ Excel บนเว็บ XLOOKUP ทำได้ทุกอย่างที่ VLOOKUP ทำ แต่พลาดน้อยกว่า คือจับคู่แบบตรงทั้งหมดเป็นค่าเริ่มต้น ค้นหาไปทางซ้ายได้ และไม่พังเมื่อแทรกคอลัมน์ ใช้ VLOOKUP เฉพาะเมื่อเวิร์กบุ๊กต้องใช้งานได้ใน Excel 2019 หรือเก่ากว่า
ข้อจำกัดเรื่องความเข้ากันได้คือข้อควรระวังหลัก XLOOKUP ไม่มีใน Excel 2016 หรือ 2019 ไฟล์ที่เต็มไปด้วยสูตร XLOOKUP จะแสดงข้อผิดพลาด #NAME? สำหรับคนที่ยังใช้เวอร์ชันเหล่านั้น
สูตรในบทความนี้แสดงตามแบบ Excel ภาษาอังกฤษ (Excel ภาษาไทยใช้ชื่อฟังก์ชันภาษาอังกฤษเหมือนกัน และใช้ , คั่นอาร์กิวเมนต์)
| VLOOKUP | XLOOKUP | |
|---|---|---|
| ค้นหาไปทางซ้าย (ส่งคืนคอลัมน์ที่อยู่ซ้ายของคอลัมน์ที่ค้นหา) | ไม่ได้ | ได้ |
| ประเภทการจับคู่เริ่มต้น | โดยประมาณ (เว้นแต่ใส่ FALSE) | ตรงทั้งหมด |
| ทนต่อการแทรกหรือลบคอลัมน์ | ไม่ เพราะหมายเลขคอลัมน์คงที่ | ได้ ช่วงจะปรับตามให้ |
| ข้อความ "ไม่พบ" ที่กำหนดเอง | ต้องใช้ IFNA หรือ IFERROR | มีในตัว (if_not_found) |
| ส่งคืนหลายคอลัมน์พร้อมกัน | ทำได้ยาก | ได้ ผลลัพธ์จะสปิลล์ |
| ค้นหาจากรายการที่ตรงล่าสุดขึ้นมา | ไม่ได้ | ได้ (search_mode -1) |
| การจับคู่โดยประมาณ | ข้อมูลต้องเรียงจากน้อยไปมาก | ค่าที่น้อยกว่าหรือมากกว่าถัดไป ไม่ต้องเรียงลำดับ |
ไวลด์การ์ด (*, ?) | ได้ เมื่อจับคู่แบบตรงทั้งหมด | ได้ เมื่อใช้ match_mode 2 |
| การค้นหาแนวนอน | ไม่ได้ ต้องใช้ HLOOKUP | ได้ ใช้ฟังก์ชันเดียวกัน |
| ความเร็ว | ไม่ต่างกันในทางปฏิบัติสำหรับเวิร์กบุ๊กทั่วไป | เท่ากัน และมีตัวเลือก binary search สำหรับข้อมูลที่เรียงแล้ว |
| มีให้ใช้ใน | Excel ทุกเวอร์ชัน | Microsoft 365, Excel 2021, Excel 2024, Excel บนเว็บ, แอปมือถือ |
เปรียบเทียบไวยากรณ์แบบเคียงข้างกัน
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
ความแตกต่างใหญ่คือวิธีบอก Excel ว่าคำตอบอยู่ที่ไหน VLOOKUP รับทั้งตารางและ หมายเลขคอลัมน์ ที่นับจากขอบซ้ายของตารางนั้น ส่วน XLOOKUP รับสองช่วงแยกกัน คือคอลัมน์ที่ใช้ค้นหาและคอลัมน์ที่ใช้ส่งคืนค่า
การค้นหาเดียวกันที่เขียนทั้งสองแบบ
นี่คือรายการสินค้าเล็กๆ ใน A1:D6 โดยมีรหัสสินค้าพิมพ์อยู่ใน G2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P-101 | Wireless Mouse | Accessories | $24.99 |
| 3 | P-102 | USB-C Hub | Accessories | $39.50 |
| 4 | P-103 | Laptop Stand | Office | $45.00 |
| 5 | P-104 | Webcam HD | Video | $59.00 |
| 6 | P-105 | Desk Lamp | Office | $32.75 |
หากต้องการหาราคาของ P-104
=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)
ทั้งสองสูตรให้ผลเป็น $59.00 VLOOKUP ค้นหาในคอลัมน์แรกของ A2:D6 และส่งคืนคอลัมน์ที่ 4 โดย FALSE บังคับให้จับคู่แบบตรงทั้งหมด ส่วน XLOOKUP ค้นหาใน A2:A6 และส่งคืนเซลล์ที่ตรงกันจาก D2:D6 โดยจับคู่แบบตรงทั้งหมดเป็นค่าเริ่มต้น
หากจะคัดลอกสูตรลงไปตามคอลัมน์ ให้ล็อกตารางด้วยการอ้างอิงแบบสัมบูรณ์ เช่น =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6) กด F4 บน Windows หรือ Cmd+T บน Mac ขณะที่เคอร์เซอร์อยู่ในการอ้างอิงเพื่อเพิ่มเครื่องหมาย $
ข้อผิดพลาดคลาสสิกสองอย่างของ VLOOKUP
1. ลืมใส่ FALSE (หรือ 0)
อาร์กิวเมนต์สุดท้ายของ VLOOKUP เป็นตัวเลือก และเมื่อคุณไม่ใส่ Excel จะใช้ การจับคู่โดยประมาณ ด้วยการจับคู่โดยประมาณ VLOOKUP จะสันนิษฐานว่าคอลัมน์แรกเรียงลำดับไว้แล้ว และส่งคืนค่าที่ใกล้เคียงที่สุดซึ่งน้อยกว่าหรือเท่ากับค่าที่ค้นหา โดยไม่มีคำเตือนใดๆ
ลองใช้ =VLOOKUP("P-110", A2:D6, 4) กับตารางข้างบน P-110 ไม่มีอยู่ แต่แทนที่จะได้ #N/A คุณจะได้ $32.75 ซึ่งเป็นราคาของ P-105 เพราะ "P-105" คือค่าที่น้อยกว่าที่ใกล้ที่สุด กับข้อมูลที่ไม่ได้เรียงลำดับ ผลลัพธ์อาจสุ่มยิ่งกว่านั้น
ควรปิดท้าย VLOOKUP แบบจับคู่ตรงทั้งหมดด้วย FALSE หรือ 0 เสมอ XLOOKUP หลีกเลี่ยงปัญหานี้เพราะใช้การจับคู่แบบตรงทั้งหมด เว้นแต่คุณสั่งเป็นอย่างอื่น
2. หมายเลขคอลัมน์ที่กำหนดตายตัว
เลข 4 ในสูตร VLOOKUP เป็นเพียงตัวเลข หากมีคนแทรกคอลัมน์ Supplier ไว้ระหว่าง Product กับ Category ตารางจะกลายเป็น A:E ราคาจะย้ายไปอยู่คอลัมน์ E และสูตรจะยังส่งคืนคอลัมน์ที่ 4 ซึ่งตอนนี้คือ Category คุณจะเห็น "Video" แทน $59.00
XLOOKUP ชี้ไปที่คอลัมน์ราคาโดยตรง (D2:D6) ดังนั้น Excel จะอัปเดตเป็น E2:E6 ให้อัตโนมัติ และคำตอบยังคงถูกต้อง
สิ่งที่ XLOOKUP ทำได้แต่ VLOOKUP ทำไม่ได้
ค้นหาไปทางซ้าย
VLOOKUP ส่งคืนได้เฉพาะคอลัมน์ที่อยู่ทางขวาของคอลัมน์ที่ค้นหา XLOOKUP ไม่สนใจทิศทาง หากต้องการหารหัสสินค้าของชื่อสินค้าใน G2
=XLOOKUP(G2, B2:B6, A2:A6)
เมื่อ G2 เป็น "Desk Lamp" สูตรนี้จะได้ P-105 แม้ว่าคอลัมน์ A จะอยู่ทางซ้ายของคอลัมน์ B
แสดงข้อความที่เป็นมิตรเมื่อไม่พบข้อมูล
=XLOOKUP(G2, A2:A6, D2:D6, "Not found")
สูตร VLOOKUP ที่เทียบเท่ากันต้องมีตัวห่อ: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found")
ส่งคืนหลายคอลัมน์ในสูตรเดียว
=XLOOKUP(G2, A2:A6, B2:D6)
สำหรับ P-104 สูตรนี้จะสปิลล์ Webcam HD, Video และ $59.00 ลงในสามเซลล์เรียงกัน ให้เว้นเซลล์เหล่านั้นไว้ว่าง ไม่เช่นนั้นจะได้ข้อผิดพลาด #SPILL!
หาค่าที่ตรงล่าสุดแทนค่าแรก
ในบันทึกที่ลูกค้าคนเดียวกันปรากฏหลายครั้ง ให้ตั้ง search_mode เป็น -1 เพื่อค้นหาจากล่างขึ้นบนและได้รายการล่าสุด
=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)
การจับคู่โดยประมาณโดยไม่ต้องเรียงลำดับ
สำหรับราคาตามระดับหรืออัตราคอมมิชชัน การจับคู่โดยประมาณของ VLOOKUP ใช้ได้เฉพาะเมื่อคอลัมน์แรกเรียงจากน้อยไปมาก match_mode -1 ของ XLOOKUP (ตรงทั้งหมด หรือรายการที่น้อยกว่าถัดไป) และ 1 (ตรงทั้งหมด หรือรายการที่มากกว่าถัดไป) ไม่ต้องใช้ข้อมูลที่เรียงลำดับ โดยมียอดสั่งซื้อขั้นต่ำใน J2:J5 และส่วนลดใน K2:K5
=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)
ไวลด์การ์ด
ทั้งสองรองรับ * และ ? แต่ XLOOKUP ต้องใช้ match_mode 2 เพื่อให้ถือเป็นไวลด์การ์ด
=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)
ทั้งสองให้ผลเป็น $32.75 สำหรับ Desk Lamp
INDEX/MATCH: ทางเลือกแบบคลาสสิก
ก่อนจะมี XLOOKUP วิธีมาตรฐานในการแก้ข้อจำกัดของ VLOOKUP คือ INDEX กับ MATCH ซึ่งค้นหาไปทางซ้ายได้ ทนต่อการแทรกคอลัมน์ และใช้ได้ใน Excel ทุกเวอร์ชัน จึงเป็นตัวเลือกที่ดีที่สุดสำหรับไฟล์ที่ต้องเปิดได้ใน Excel 2019 หรือเก่ากว่า
=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))
สูตรแรกส่งคืนราคาสำหรับรหัสใน G2 ส่วนสูตรที่สองเป็นตัวอย่างการค้นหาไปทางซ้าย เลข 0 ใน MATCH หมายถึงจับคู่แบบตรงทั้งหมด และเช่นเดียวกับ FALSE ของ VLOOKUP ที่ลืมได้ง่าย XLOOKUP ก็คือแนวคิดเดียวกันในเวอร์ชันที่สั้นกว่าและปลอดภัยกว่า
วิธีแปลงสูตร VLOOKUP เป็น XLOOKUP
ไม่มีตัวแปลงในตัว แต่การแปลงทำตามขั้นตอนได้แน่นอน
- หา VLOOKUP ของคุณ: กด
Ctrl+F(Cmd+Fบน Mac) พิมพ์ VLOOKUP คลิก ตัวเลือก (Options) หากการตั้งค่าเพิ่มเติมถูกซ่อนอยู่ ตั้ง มองใน (Look in) เป็น สูตร (Formulas) แล้วคลิก ค้นหาทั้งหมด (Find All) - เก็บอาร์กิวเมนต์แรก (ค่าที่ค้นหา) ไว้ตามเดิม
- แทนที่ตารางด้วย คอลัมน์แรก ของตารางเป็น
lookup_array - แทนที่หมายเลขคอลัมน์ด้วย คอลัมน์ที่ตรงกัน เป็น
return_arrayคอลัมน์ที่ 5 ของตารางที่เริ่มต้นที่คอลัมน์ A คือคอลัมน์ E - ตัด
FALSEออก หากต้นฉบับลงท้ายด้วยTRUEหรือไม่มีอาร์กิวเมนต์สุดท้าย ให้เพิ่มmatch_mode-1 เพื่อคงพฤติกรรม "ค่าที่น้อยกว่าถัดไป"
ตัวอย่างเช่น
=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)
ทดสอบผลลัพธ์สองสามรายการเทียบกับสูตรเดิมก่อนแปลงทั้งชีต VLOOKUP แบบจับคู่โดยประมาณกับข้อมูลที่ไม่ได้เรียงลำดับอาจส่งคืนค่าผิดมาตลอด ดังนั้นคำตอบของ XLOOKUP อาจต่างออกไปได้อย่างสมเหตุสมผล
จะเกิดอะไรขึ้นเมื่อเปิดไฟล์ XLOOKUP ใน Excel 2016 หรือ 2019
เวิร์กบุ๊กจะเปิดได้ แต่เวอร์ชันเหล่านั้นไม่รู้จักฟังก์ชันนี้ ในแถบสูตรจะแสดงเป็น =_xlfn.XLOOKUP(...) เซลล์อาจยังแสดงผลลัพธ์ที่บันทึกไว้ล่าสุดในตอนแรก แต่เมื่อสูตรคำนวณใหม่ก็จะส่งคืน #NAME?
หากคุณแชร์ไฟล์กับคนที่ใช้เวอร์ชันเก่า
- ใช้ INDEX/MATCH หรือ VLOOKUP แทน หรือ
- ส่งสำเนาที่วางผลลัพธ์เป็นค่า (คัดลอก (Copy) → วางแบบพิเศษ (Paste Special) → ค่า (Values))
เช่นเดียวกันกับฟังก์ชันใหม่อื่นๆ เช่น FILTER, UNIQUE และ XMATCH
XLOOKUP ใน Google Sheets
Google Sheets ก็มี XLOOKUP เช่นกัน โดยลำดับอาร์กิวเมนต์เหมือนกัน: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) สูตรในบทความนี้ใช้ได้ตามที่เขียนไว้ XLOOKUP จึงเป็นตัวเลือกที่ปลอดภัยสำหรับไฟล์ที่ย้ายไปมาระหว่าง Excel กับ Sheets
ทำไมการค้นหาจึงได้ #N/A หรือค่าที่ผิด
- ช่องว่างเกิน "P-104 " ที่มีช่องว่างต่อท้ายจะไม่ตรงกับ "P-104" ทำความสะอาดข้อมูลด้วย
TRIM - ตัวเลขที่เก็บเป็นข้อความ รหัสที่พิมพ์เป็นข้อความจะไม่ตรงกับรหัสเดียวกันที่เก็บเป็นตัวเลข มองหาสามเหลี่ยมสีเขียวที่มุมเซลล์ แล้วแปลงคอลัมน์ให้เป็นชนิดเดียวกัน
- ไม่ได้ใส่ FALSE ใน VLOOKUP คุณจะได้คำตอบที่ดูสมเหตุสมผลแต่ผิด แทนที่จะเป็น
#N/A - ช่วงที่ขยับเมื่อคัดลอกสูตร ใช้การอ้างอิงสัมบูรณ์
$สำหรับตารางค้นหา #REF!จาก VLOOKUP หมายเลขคอลัมน์มากกว่าจำนวนคอลัมน์ในตาราง#VALUE!จาก XLOOKUP ช่วงค้นหาและช่วงส่งคืนมีขนาดต่างกัน เช่น A2:A6 กับ D2:D60#NAME?สะกดชื่อฟังก์ชันผิด หรือเปิดไฟล์ในเวอร์ชันที่ไม่มี XLOOKUP
คำถามที่พบบ่อย
XLOOKUP ดีกว่า VLOOKUP ไหม ดีกว่าสำหรับเกือบทุกการใช้งาน จับคู่แบบตรงทั้งหมดเป็นค่าเริ่มต้น ค้นหาได้ทุกทิศทาง ไม่พังเมื่อคอลัมน์ย้าย และจัดการค่าที่ไม่พบได้โดยไม่ต้องมีตัวห่อ ข้อได้เปรียบจริงๆ อย่างเดียวของ VLOOKUP คือใช้ได้ใน Excel ทุกเวอร์ชัน
XLOOKUP เร็วกว่า VLOOKUP ไหม ในเวิร์กบุ๊กทั่วไปคุณจะไม่รู้สึกถึงความแตกต่าง กับรายการเรียงลำดับขนาดใหญ่มาก โหมด binary search ของ XLOOKUP (search_mode 2 หรือ -2) ช่วยให้เร็วขึ้นได้ แต่จะให้ผลลัพธ์ผิดหากข้อมูลไม่ได้เรียงลำดับ
ทำไม XLOOKUP ขึ้น #NAME? บนคอมพิวเตอร์ของฉัน Excel เวอร์ชันของคุณไม่มี XLOOKUP ฟังก์ชันนี้มีใน Microsoft 365, Excel 2021 ขึ้นไป และ Excel บนเว็บ แต่ไม่มีใน Excel 2019 หรือ 2016 ให้ใช้ INDEX/MATCH แทน
XLOOKUP มาแทน HLOOKUP ด้วยไหม ใช่ หากช่วงค้นหาและช่วงส่งคืนเป็นแถวแทนคอลัมน์ XLOOKUP จะค้นหาในแนวนอนด้วยไวยากรณ์เดียวกัน
XLOOKUP ส่งคืนทุกรายการที่ตรงได้ไหม ไม่ใช่แค่รายการแรก ไม่ได้ ส่งคืนหนึ่งรายการที่ตรง (หรือหนึ่งแถวของคอลัมน์) หากต้องการทุกแถวที่ตรง ให้ใช้ FILTER เช่น =FILTER(A2:D6, C2:C6="Office")
ควรเรียนรู้ VLOOKUP อยู่ไหม ควรรู้จักไว้ เพราะคุณจะพบในเวิร์กบุ๊กและเทมเพลตเก่าๆ สำหรับสูตรใหม่ ให้เรียนรู้ XLOOKUP และจำ INDEX/MATCH ไว้สำหรับไฟล์ที่ต้องเปิดใน Excel เวอร์ชันเก่า
ให้ AI เขียนสูตรค้นหาให้คุณ
สูตรค้นหาเป็นจุดที่รายละเอียดเล็กๆ เช่น ลืม FALSE หรือช่วงที่เลื่อน ทำให้เกิดข้อผิดพลาดเงียบๆ มากที่สุด CoreGPT for Excel ให้คุณอธิบายสิ่งที่ต้องการ เช่น "หาราคาของรหัสสินค้าใน G2" หรือ "ทำเครื่องหมายแต่ละแถวว่าสูงกว่าหรือต่ำกว่าเป้าหมาย" แล้วเขียนสูตรลงในชีตของคุณพร้อมคำอธิบายสั้นๆ เริ่มใช้ได้ฟรี และใช้งานได้ใน Excel สำหรับ Windows, Mac และบนเว็บ
CoreGPTใช้สูตรนี้แล้วเติมลงด้านล่าง:
=IF(C2>=D2,"สูงกว่า","ต่ำกว่า")ลองใช้งาน: CoreGPT for Excel
อ่านเพิ่มเติม:
