8 เทคนิคการใช้ XLOOKUP
ฉบับสมบูรณ์
หลายคนเริ่มใช้ XLOOKUP เพราะต้องการค้นหาข้อมูลแทน VLOOKUP แต่จริง ๆ แล้วฟังก์ชันนี้ทำได้มากกว่าการค้นหาแบบทั่วไปครับ
ในบทความนี้ เราจะลองใช้ XLOOKUP แก้โจทย์ที่พบบ่อยในการทำงาน พร้อมตัวอย่างที่สามารถเปิด Excel แล้วทำตามได้ทันที
XLOOKUP คืออะไร?
XLOOKUP เป็นฟังก์ชันสำหรับค้นหาข้อมูลจากตาราง โดยกำหนดช่วงค้นหาและช่วงผลลัพธ์แยกจากกัน จึงค้นหาได้ทั้งแนวตั้ง แนวนอน และส่งคำตอบจากด้านซ้ายหรือด้านขวาของตารางได้
รองรับ Microsoft Excel 2021 ขึ้นไป และ Microsoft 365
รูปแบบการใช้งาน
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])คำอธิบาย
- lookup_value
- ค่าที่ต้องการค้นหา เช่น รหัสสินค้า หรือรหัสพนักงาน
- lookup_array
- ช่วงข้อมูลที่ใช้ค้นหา
- return_array
- ช่วงข้อมูลที่ต้องการคำตอบ
- if_not_found (ไม่บังคับ)
- ข้อความหรือค่าที่ต้องการแสดงเมื่อค้นหาไม่พบ
- match_mode (ไม่บังคับ)
- กำหนดรูปแบบการจับคู่ เช่น ตรงเป๊ะ ค่าที่ใกล้เคียง หรือ Wildcard
- search_mode (ไม่บังคับ)
- กำหนดทิศทางการค้นหา เช่น บนลงล่าง หรือล่างขึ้นบน
เทคนิคที่ 1 ค้นหาข้อมูลตามช่วงคะแนน
สมมติว่าต้องการเทียบคะแนน KPI ของพนักงานแต่ละคนกับตารางเกรด หากคะแนนไม่มีอยู่ในตารางแบบตรงตัว ให้ XLOOKUP เลือกค่าที่น้อยกว่าและใกล้เคียงที่สุด
ที่เซลล์ E2 ให้พิมพ์สูตรดังนี้
=XLOOKUP(D2,$G$2:$G$7,$I$2:$I$7,"ไม่พบข้อมูล",-1)คำอธิบาย
- เราจะใช้ match_mode = -1 เพื่อหาค่าที่น้อยกว่าและใกล้เคียงที่สุด
- หากคะแนน 85 ไม่มีอยู่ในตาราง Excel จะเลือก 80 แล้วส่งเกรดของแถวนั้นกลับมา ก็คือ B+ นั่นเอง
หมายเหตุ: การใช้ match_mode = -1 เหมาะกับการค้นหาข้อมูลที่เป็น "ช่วง" เช่น คะแนน เกรด ส่วนลด ค่าคอมมิชชัน และขั้นภาษี เป็นต้น
เทคนิคที่ 2 ค้นหาข้อมูลแบบตรงเป๊ะ
เมื่อต้องค้นหารหัสสินค้า รหัสพนักงาน หรือเลขที่เอกสาร ข้อมูลต้องตรงกันทุกตัวอักษร จากตัวอย่างนี้ต้องการค้นหา PartNo แล้วส่ง UnitPrice ของรายการที่พบมาแสดง
ที่เซลล์ H2 ให้พิมพ์สูตรดังนี้
=XLOOKUP(H1,A2:A74,E2:E74,"ไม่พบข้อมูล")คำอธิบาย
- XLOOKUP จะค้นหา PartNo ในเซลล์ H1 จากช่วง A2:A74
- เมื่อพบข้อมูล จะส่ง UnitPrice จากช่วง E2:E74 กลับมา
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล" แทนข้อผิดพลาด #N/A
- สูตรนี้ใช้ Exact Match (match_mode = 0) ซึ่งเป็นค่าเริ่มต้นของ XLOOKUP จึงไม่จำเป็นต้องระบุในสูตร
หมายเหตุ: การใช้ match_mode = 0 เหมาะกับการค้นหาข้อมูลที่เป็น รหัสสินค้า, รหัสพนักงาน, เลขที่เอกสาร, เลขใบสั่งซื้อ และข้อมูลที่ต้องค้นหาแบบตรงกันทุกตัวอักษร
เทคนิคที่ 3 ค้นหาข้อมูลจากขวาไปซ้าย
XLOOKUP กำหนดช่วงค้นหาและช่วงผลลัพธ์ได้อย่างอิสระ จึงค้นหาจากขวาไปซ้ายได้โดยไม่ต้องสลับตำแหน่งคอลัมน์เหมือน VLOOKUP
ที่เซลล์ H2 ให้พิมพ์สูตรดังนี้
=XLOOKUP(H1,C2:C74,B2:B74,"ไม่พบข้อมูล")คำอธิบาย
- XLOOKUP จะค้นหา SerialNo ในเซลล์ H1 จากช่วง C2:C74
- เมื่อพบข้อมูล จะส่ง Material description จากช่วง B2:B74 กลับมา
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล" แทนข้อผิดพลาด #N/A
- สูตรนี้ใช้ Exact Match (match_mode = 0) ซึ่งเป็นค่าเริ่มต้นของ XLOOKUP จึงไม่จำเป็นต้องระบุในสูตร
หมายเหตุ: XLOOKUP สามารถกำหนดช่วงค้นหา (lookup_array) และช่วงผลลัพธ์ (return_array) ได้อย่างอิสระ จึงค้นหาข้อมูลได้ทั้งสองทิศทาง
เทคนิคที่ 4 ค้นหาข้อมูลแบบหลายเงื่อนไข
หากต้องใช้ Material description ร่วมกับ Location เพื่อหา UnitPrice สามารถใช้เครื่องหมาย * เชื่อมเงื่อนไขให้ทำงานเหมือน AND โดยไม่ต้องสร้างคอลัมน์ช่วย
ที่เซลล์ H3 ให้พิมพ์สูตรดังนี้
=XLOOKUP(1,(B2:B74=H1)*(D2:D74=H2),E2:E74,"ไม่พบข้อมูล")คำอธิบาย
- (B2:B74=H1) ตรวจสอบว่า Material Description ตรงกับข้อมูลที่ค้นหาหรือไม่
- (D2:D74=H2) ตรวจสอบว่า Location ตรงกับข้อมูลที่ค้นหาหรือไม่
- เครื่องหมาย * ทำหน้าที่เหมือน AND โดยจะส่งค่า 1 เมื่อทั้งสองเงื่อนไขเป็นจริง และส่งค่า 0 หากมีเงื่อนไขใดไม่ตรง
- XLOOKUP(1,...) จึงค้นหาค่า 1 และส่ง UnitPrice ของแถวนั้นกลับมา
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล"
หมายเหตุ: เทคนิคนี้เหมาะกับการค้นหาข้อมูลที่ใช้มากกว่าหนึ่งเงื่อนไข เช่น รหัสสินค้า + สาขา หรือ สินค้า + คลังสินค้า โดยไม่ต้องสร้างคอลัมน์ช่วย (Helper Column)
เทคนิคที่ 5 ค้นหาข้อมูลแบบ 2 มิติ
เมื่อต้องค้นหาข้อมูลจากตารางที่มีหัวข้อกำกับทั้งแนวตั้งและแนวนอน เช่น ต้องการหาตัวเลขที่ตัดกันระหว่างประเทศกับหมวดสินค้าที่ระบุ จะต้องค้นหาทั้งแถวของประเทศและคอลัมน์ของหมวดสินค้า จึงใช้ XLOOKUP สองชุดซ้อนกันเพื่อหาค่าที่ตำแหน่งตัดกัน
ที่เซลล์ I4 ให้พิมพ์สูตรดังนี้
=XLOOKUP(I1,A2:A16,XLOOKUP(I2,B1:F1,B2:F16,"ไม่พบข้อมูล"))คำอธิบาย
- XLOOKUP ด้านใน ใช้ค้นหาคอลัมน์ที่ต้องการ เช่น หมวดสินค้า ผลลัพธ์ที่ได้จะเป็นช่วงข้อมูลของหมวดสินค้านั้นทั้งคอลัมน์
- XLOOKUP ด้านนอก ใช้ค้นหาประเทศ แล้วดึงข้อมูลจากคอลัมน์ที่ได้ในขั้นตอนแรก
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล"
หมายเหตุ: เทคนิคนี้ช่วยให้ค้นหาข้อมูลแบบ 2 มิติ ได้โดยไม่ต้องใช้ INDEX ร่วมกับ MATCH หลายตัว ทำให้สูตรอ่านง่ายและแก้ไขได้สะดวกกว่า
เทคนิคที่ 6 ค้นหารายการล่าสุด
โดยปกติ XLOOKUP ค้นหาจากบนลงล่าง แต่หากต้องการวันที่สั่งซื้อล่าสุดของลูกค้า ให้ใช้ search_mode = -1 เพื่อค้นหาจากล่างขึ้นบน
ที่เซลล์ F3 ให้พิมพ์สูตรดังนี้
=XLOOKUP(F1,B2:B11,C2:C11,"ไม่พบข้อมูล",,-1)คำอธิบาย
- Search Mode = -1 คือ ให้ XLOOKUP ค้นหาจากล่างขึ้นบน
- หากมีข้อมูลซ้ำหลายรายการ สูตรจะส่งค่าจากรายการล่าสุดกลับมา
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล"
หมายเหตุ: เทคนิคนี้เหมาะกับข้อมูลที่มีการบันทึกซ้ำหลายครั้ง เช่น ราคาสินค้าล่าสุด, สถานะล่าสุด, วันที่อัปเดตล่าสุด หรือข้อมูลการรับ–จ่ายสินค้า โดยไม่ต้องเรียงลำดับข้อมูลใหม่หรือใช้สูตรที่ซับซ้อน
เทคนิคที่ 7 ส่งผลลัพธ์หลายคอลัมน์พร้อมกัน
XLOOKUP ส่งผลลัพธ์หลายคอลัมน์ได้ในครั้งเดียว ตัวอย่างนี้ค้นหา PartNo แล้วแสดง Material description, SerialNo, Location และ UnitPrice พร้อมกัน
ที่เซลล์ H2 ให้พิมพ์สูตรดังนี้
=TRANSPOSE(XLOOKUP(H1,A2:A74,B2:E74,"ไม่พบข้อมูล"))คำอธิบาย
- A2:A74 คือช่วงข้อมูลที่ใช้ค้นหา PartNo
- B2:E74 คือช่วงข้อมูลที่ต้องการส่งกลับ ซึ่งมี 4 คอลัมน์
- เมื่อค้นหาพบ XLOOKUP จะส่งผลลัพธ์ทั้ง 3 คอลัมน์ออกมาพร้อมกัน (Spill)
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล"
- เราจะใช้ฟังก์ชัน TRANSPOSE ในการกำหนดทิศทางของการกระจายค่าตามแนวที่ต้องการ ในที่นี้คือต้องการกระจายค่าตามแนวตั้ง
หมายเหตุ: หากผลลัพธ์แสดงข้อผิดพลาด #SPILL! ให้ตรวจสอบว่ามีข้อมูลอยู่ในเซลล์ด้านขวาของสูตรหรือไม่ เพราะ XLOOKUP ต้องใช้พื้นที่ว่างในการแสดงผลลัพธ์หลายคอลัมน์
เทคนิคที่ 8 ค้นหาข้อมูลด้วย Wildcard
หากจำชื่อสินค้าได้เพียงบางส่วน ให้ใช้ Wildcard ร่วมกับ XLOOKUP เพื่อค้นหาคำขึ้นต้น คำลงท้าย หรือข้อความบางส่วน
ที่เซลล์ H2 ให้พิมพ์สูตรดังนี้
=XLOOKUP(H1,B2:B74,E2:E74,"ไม่พบข้อมูล",2)คำอธิบาย
- "*" คือ Wildcard ที่แทนข้อความได้ตั้งแต่ 0 ตัวอักษรขึ้นไป
- จากตัวอย่างระบุ SWOPPER*BN หมายถึง ขึ้นต้นด้วยคำว่า SWOPPER และลงท้ายด้วยคำว่า BN ระหว่างคำคือข้อความใดก็ได้
- 2 คือ Match Mode สำหรับการค้นหาแบบ Wildcard
- หากค้นหาไม่พบ จะแสดงข้อความ "ไม่พบข้อมูล"
หมายเหตุ: นอกจาก * แล้ว ยังสามารถใช้ ? เพื่อแทนตัวอักษร 1 ตัว ได้ เช่น A?C จะค้นหา ABC, ADC หรือ A1C แต่จะไม่ตรงกับ ABBC
