Excel มีเทคนิค Lookup มากมายสำหรับจัดการและวิเคราะห์ข้อมูล VLOOKUP ใช้ดึงข้อมูลทั่วไป แต่มีข้อจำกัด: ต้องมีคอลัมน์ Lookup ด้านซ้าย และจัดการ Error ไม่ยืดหยุ่น เพื่อแก้ปัญหานี้ ฟังก์ชันขั้นสูงอย่าง XLOOKUP และ INDEX-MATCH-MATCH ให้ความยืดหยุ่น ควบคุม และประสิทธิภาพมากกว่า มาดูเทคนิค Lookup ขั้นสูงเหนือ VLOOKUP ด้วย XLOOKUP และ INDEX-MATCH-MATCH
ใช้ชุดข้อมูลขาย (Sales Dataset) สาธิต Advanced Lookup Techniques ด้วยตัวอย่างปฏิบัติ
เทคนิค XLOOKUP ขั้นสูง
XLOOKUP เป็นฟังก์ชันหลากหลายสำหรับ Lookup คอลัมน์เดี่ยวหรือหลายคอลัมน์ ค้นหาได้ทุกทิศทาง (ซ้าย-ขวา, ขวา-ซ้าย, แนวตั้ง, แนวนอน) มี Custom Error Message ไม่ต้องจัดเรียงข้อมูลก่อน Lookup XLOOKUP มีเฉพาะ Excel 2021 และ MS Office 365 อัปเดตข้อมูล Lookup Values อัตโนมัติ
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: ค่าที่ต้องการค้นหา
- lookup_array: ช่วง/อาร์เรย์ที่ค้นหา
- return_array: ช่วง/อาร์เรย์ที่คืนผลลัพธ์
- [if_not_found] [Optional]: ค่าที่คืนถ้าไม่เจอ
- [match_mode] [Optional]: ประเภทการ Match
- 0 – Exact match (Default)
- 1 – Exact หรือค่าถัดไปที่มากกว่า
- -1 – Exact หรือค่าถัดไปที่น้อยกว่า
- 2 – Wildcard match
- [search_mode] [Optional]: ทิศทางการค้นหา
- 1 – แรกไปสุด (Default)
- -1 – สุดไปแรก
- 2 – Binary search (昇序)
- -2 – Binary search (降序)
1. XLOOKUP สำหรับ Single Criteria
หายอดขายที่ใกล้ $100 มากที่สุด จาก Dataset เพื่อดูลูกค้าที่ใช้จ่ายใกล้ $100 ใส่สูตร:
สูตร:
=XLOOKUP(100, G2:G71, A2:G71, “Not Found”, 1)
สูตรค้นหา 100 ใน G2:G71 คืนแถวที่ตรงจาก A2:G71 ถ้าไม่เจอค่าที่ใกล้ที่สุด แสดง “Not Found”
ผลลัพธ์:
1007 1/4/2024 Daniel Martinez East 39.99 3 119.97
2. XLOOKUP ด้วย Multiple Criteria
Lookup ซับซ้อนด้วยการ Concatenate หลายเงื่อนไข ใช้ XLOOKUP สูตร:
สูตร:
=XLOOKUP(“Melissa Lopez” & “West”, C2:C71 & D2:D71, A2:G71)
สูตรรวมชื่อลูกค้าและ Region ค้นหาค่ารวมใน Lookup Arrays ที่รวมกัน คืนข้อมูลจากช่วงที่เลือก
ผลลัพธ์:
1012 1/6/2024 Melissa Lopez West 79.99 2 159.98
INDEX-MATCH-MATCH สำหรับ Advanced Lookup
INDEX-MATCH-MATCH ใช้เมื่อ Lookup ต้องอาศัยทั้ง Row และ Column Criteria เหมาะกับตาราง 2D
Syntax:
=INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), MATCH(column_lookup_value, column_lookup_array, 0))
- array: ช่วงเซลล์ที่มีค่าที่ต้องการดึง
- MATCH(row_lookup_value, row_lookup_array, 0): คืนหมายเลขแถว
- row_lookup_value: ค่าค้นหาในแถว
- row_lookup_array: ช่วงแถวค้นหา
- 0 – Exact match
- MATCH(column_lookup_value, column_lookup_array, 0): คืนหมายเลขคอลัมน์
- column_lookup_value: ค่าค้นหาในคอลัมน์
- column_lookup_array: ช่วงคอลัมน์ค้นหา
- 0 – Exact match
1. 2D Lookups สำหรับ Row และ Column
ดูยอดขาย (Sales Amount) ของลูกค้าเฉพาะ ใช้ INDEX-MATCH-MATCH ใส่สูตร:
=INDEX(A2:G71, MATCH(“John Smith”, C2:C71, 0), MATCH(“Sales Amount”, A1:G1, 0))
สูตรค้นหา John Smith ใน C2:C71 และ “Sales Amount” ใน A1:G1 คืนค่าตัดกันใน A2:G71
ผลลัพธ์:
99.98
2. Advanced INDEX-MATCH-MATCH สำหรับ 3D Lookup
INDEX-MATCH-MATCH ปกติเป็น 2D แต่ขยายเป็น 3D Lookup ได้ด้วย Array Formula หลาย MATCH เหมาะกับ Dataset ใหญ่และ Criteria ซับซ้อน ทรงพลังกว่า VLOOKUP
สูตร:
=INDEX(G2:G71, MATCH(1, (C2:C71=”John Smith”) * (D2:D71=”South”) * (F2:F71=4), 0))
สูตรค้นหา Sales Amount โดย Match 3 เงื่อนไข: Customer Name (คอลัมน์ C), Region (คอลัมน์ D), Quantity (คอลัมน์ F) เงื่อนไขรวมด้วยการคูณเป็น AND Function ให้ MATCH หาแถวที่ตรงทุกเงื่อนไข INDEX คืนค่าจาก G2:G71
ผลลัพธ์:
129.99
ข้อดีของ XLOOKUP และ INDEX-MATCH
ทำไมเลือก XLOOKUP แทน VLOOKUP?
- Bidirectional Search: XLOOKUP ค้นหาทั้ง 2 ทิศทาง VLOOKUP ค้นหาซ้าย-ขวาเท่านั้น
- No Column Index Number: ไม่ต้องระบุ Column Index การเปลี่ยนคอลัมน์ไม่กระทบ
- Default Exact Match: ค้นหา Exact Match โดย Default ลด Error
- Wildcard Searches: ใช้ Wildcards (* ?) กับ XLOOKUP ได้
- Error Handling: ระบุค่าที่คืนถ้าไม่เจอได้
ข้อดีของ INDEX-MATCH แทน VLOOKUP
- Flexible Table Structure: ไม่ได้รับผลกระทบจากการ Insert/Delete คอลัมน์
- Left Lookup: ค้นหาด้านซ้ายได้ ไม่เหมือน VLOOKUP
- Performance: มีประสิทธิภาพใน Dataset ใหญ่ MATCH เร็วกว่า VLOOKUP บางกรณี
สรุป
Advanced Lookup Techniques อย่าง XLOOKUP และ INDEX-MATCH-MATCH จำเป็นสำหรับจัดการข้อมูลขั้นสูงใน Excel ฟังก์ชันเหล่านี้อัปเดตการเปลี่ยนแปลงข้อมูลอัตโนมัติ ให้ความยืดหยุ่น ความแม่นยำ และประสิทธิภาพเกิน VLOOKUP ทำให้สำคัญสำหรับผู้ที่ต้องการพัฒนาทักษะ Excel ระดับสูง
Get FREE Advanced Excel Exercises with Solutions!
