Thep Excel
XLOOKUPฟังก์ชันค้นหาข้อมูลแนวตั้งและแนวนอน
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| lookup_value | Any | ค่าที่ต้องการค้นหา | |
| lookup_array | Range | ช่วงข้อมูลที่ใช้ในการค้นหา | |
| return_array | Range | ช่วงข้อมูลที่ต้องการให้แสดงผลลัพธ์ | |
| [if_not_found]ไม่บังคับ | Any | #N/A | ค่าที่แสดงเมื่อไม่พบข้อมูล (ถ้าไม่ระบุจะแสดง #N/A) |
| [match_mode]ไม่บังคับ | Number | 0 | โหมดการจับคู่: 0 = ตรงทุกประการ -1 = ตรงทุกประการหรือน้อยกว่า 1 = ตรงทุกประการหรือมากกว่า 2 = ใกล้เคียง (wildcard) |
| [search_mode]ไม่บังคับ | Number | 1 | โหมดการค้นหา: 1 = ค้นหาจากต้นไปท้าย -1 = ค้นหาจากท้ายไปต้น 2 = binary search (เรียงจากน้อยไปมาก) -2 = binary search (เรียงจากมากไปน้อย) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
ฟังก์ชัน XLOOKUP เป็นฟังก์ชันค้นหาข้อมูลรุ่นใหม่ที่ทรงพลังกว่า VLOOKUP และ HLOOKUP มาก สามารถค้นหาได้ทั้งแนวตั้งและแนวนอน รองรับการค้นหาแบบย้อนกลับ และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้เลย
ที่เจ๋งคือไม่ต้องนับลำดับคอลัมน์เหมือน VLOOKUP ครับ แค่ระบุช่วงที่ต้องการให้แสดงผลได้เลย ยืดหยุ่นกว่าเยอะมาก
ส่วนตัวผมแนะนำให้ใช้ XLOOKUP แทน VLOOKUP ทุกครั้งถ้ามี Excel 365 หรือ 2021 นะครับ 😎
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | รหัสสินค้า | ชื่อสินค้า | สถานะ | ราคา |
| 2 | P001 | เมาส์ไร้สาย | ปิด | 590 |
| 3 | P002 | คีย์บอร์ด | เปิด | 1,290 |
| 4 | P003 | จอ 27 นิ้ว | ปิด | 8,900 |
| 5 | P004 | หูฟัง | เปิด | 2,450 |
| 6 | P005 | เว็บแคม | ปิด | 1,750 |
ทุกสูตรด้านล่างอ้างถึงตารางนี้ · พิมพ์ตามได้เลย ผลลัพธ์จะตรงกับที่เขียนไว้
ตัวอย่างที่ 1: ค้นหาพื้นฐาน (Exact Match)XLOOKUP("P003", A2:A6, D2:D6)
หา "P003" ในคอลัมน์ A แล้วคืนค่าจากคอลัมน์ D ที่แถวเดียวกัน ซึ่งก็คือราคา 8,900 บาท
สังเกตว่าไม่ต้องนับเลยครับว่าราคาเป็นคอลัมน์ที่เท่าไหร่ แค่ชี้ช่วงที่อยากได้ตรงๆ ตรงนี้แหละที่ต่างจาก VLOOKUP ชัดที่สุด
ตัวอย่างที่ 2: กำหนดค่าเมื่อไม่พบข้อมูลXLOOKUP("P099", A2:A6, B2:B6, "ไม่พบสินค้านี้")
ในตารางไม่มี P099 ปกติสูตรจะคืน #N/A ออกมา แต่พอใส่ค่าที่สี่เข้าไป มันจะแสดงข้อความที่เรากำหนดแทน
ที่ดีคือไม่ต้องเอา IFERROR มาห่ออีกชั้นครับ สูตรสั้นลงและอ่านรู้เรื่องกว่าเดิมเยอะ
ตัวอย่างที่ 3: ค้นหาจากล่างขึ้นบน (ย้อนกลับ)XLOOKUP("ปิด", C2:C6, B2:B6, , 0, -1)
ในตารางมีสถานะ "ปิด" อยู่ 3 แถว คือ P001, P003 และ P005 ถ้าค้นแบบปกติจะได้ "เมาส์ไร้สาย" เพราะเจอตัวบนสุดก่อน
แต่พอใส่ -1 ในช่องสุดท้าย มันจะไล่จากล่างขึ้นบนแทน เลยได้ "เว็บแคม" ซึ่งเป็นแถวล่างสุดที่สถานะปิด
เวลาทำงานจริงผมใช้ท่านี้กับตารางที่เรียงตามวันที่ครับ เพราะแถวล่างสุดคือรายการล่าสุด
ตัวอย่างที่ 4: ค้นหาและแสดงผลหลายคอลัมน์XLOOKUP("P003", A2:A6, B2:D6)
คราวนี้ช่วงที่ให้คืนค่ากว้างสามคอลัมน์ (B ถึง D) สูตรเลยคืนทั้งแถวออกมาพร้อมกัน แล้วกระจายลงเซลล์ข้างๆ ให้เอง
เขียนสูตรเดียวได้ข้อมูลครบแถว ไม่ต้องก๊อปสูตรไปทีละช่องแบบเมื่อก่อนครับ
FAQs
XLOOKUP ต่างจาก VLOOKUP อย่างไร?+
XLOOKUP สามารถค้นหาจากซ้ายไปขวาหรือขวาไปซ้ายได้ ไม่ต้องนับตำแหน่งคอลัมน์ และสามารถกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
เอาจริงๆ ถ้ามี Excel 365 หรือ 2021 แนะนำให้ใช้ XLOOKUP แทนเลยครับ ทำงานได้ดีกว่าและยืดหยุ่นกว่าเยอะ 😎
XLOOKUP ใช้ได้กับ Excel เวอร์ชันไหน?+
XLOOKUP ใช้ได้กับ Excel 365 และ Excel 2021 เท่านั้น ไม่รองรับเวอร์ชันเก่ากว่า
ที่ต้องระวังคือถ้าส่งไฟล์ให้คนที่ใช้ Excel เวอร์ชันเก่า สูตรจะแสดง #NAME? error ครับ
XLOOKUP สามารถแทน INDEX/MATCH ได้หรือไม่?+
ได้ครับ XLOOKUP ถูกออกแบบมาเพื่อแทนที่ VLOOKUP HLOOKUP และ INDEX/MATCH โดยมีไวยากรณ์ที่เข้าใจง่ายกว่า
ส่วนตัวผมคิดว่า XLOOKUP อ่านสูตรง่ายกว่า INDEX/MATCH เยอะมากเลยครับ 💡
match_mode และ search_mode คืออะไร?+
match_mode กำหนดวิธีการจับคู่ข้อมูล (ตรงทุกประการ/ใกล้เคียง) ส่วน search_mode กำหนดทิศทางการค้นหา (บนลงล่าง/ล่างขึ้นบน)
ส่วนตัวผมใช้ค่าเริ่มต้น (match_mode=0, search_mode=1) บ่อยที่สุดครับ ค้นหาแบบตรงทุกประการจากบนลงล่าง
XLOOKUP สามารถค้นหาหลายคอลัมน์พร้อมกันได้หรือไม่?+
ได้ครับ สามารถระบุ return_array เป็นช่วงหลายคอลัมน์ เช่น B2:D10 เพื่อแสดงผลหลายคอลัมน์พร้อมกัน
ที่เจ๋งคือผลลัพธ์จะกระจายออกมาเป็น spill array อัตโนมัติเลยครับ ไม่ต้องลากสูตร 😎
จะทำอย่างไรถ้า XLOOKUP ไม่พบข้อมูล?+
ใช้ argument if_not_found เพื่อกำหนดค่าที่ต้องการแสดงเมื่อไม่พบข้อมูล แทนที่จะแสดง #N/A
ส่วนตัวผมใช้แบบนี้เป็นประจำเลยครับ ทำให้สูตรสะอาดขึ้นและไม่ต้องใช้ IFERROR อีกชั้น 💡
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ XLOOKUP จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
GETPIVOTDATA ช่วยดึงข้อมูลจาก PivotTable โดยอ้างอิงชื่อฟิลด์และเงื่อนไข แทนที่จะอ้างอิงตำแหน่งเซลล์
.
เหมาะสำหรับการสร้างรายงานแบบไดนามิกและการวิเคราะห์ข้อมูลที่ต้องการความแม่นยำสูง เพราะสูตรจะยังคงทำงานได้ถูกต้องแม้ PivotTable จะเปลี่ยนแปลง 💡
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มีเทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้
UNIQUE เป็น Dynamic Array Function ที่คืนค่าที่ไม่ซ้ำจาก Array โดยสามารถตรวจซ้ำตามแถวหรือคอลัมน์ (by_col) และเลือกคืนเฉพาะค่าที่พบครั้งเดียว (exactly_once) ผลลัพธ์เป็น Spill Range ที่อัปเดตอัตโนมัติ ใช้ร่วมกับ SORT FILTER COUNTIF เพื่อสร้างรายงานไดนามิกและ dropdown ที่อัปเดตเอง
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า
IFNA เป็นฟังก์ชันที่ช่วยจัดการ #N/A errors โดยแทนค่าเป็นข้อความหรือค่าอื่นที่คุณกำหนด เหมาะสำหรับการค้นหาข้อมูลที่อาจไม่พบผลลัพธ์
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่