XLOOKUP(): ฟังก์ชันค้นหาที่หลากหลายที่สุดของ Excel

หากเคยใช้ VLOOKUP() แล้วติดขัดเพราะต้องค้นหาค่าทางซ้าย XLOOKUP() แก้ปัญหานั้นให้ได้ มันเป็นฟังก์ชันค้นหารุ่นใหม่ของ Excel ที่สามารถแทนที่ VLOOKUP(), HLOOKUP() และแม้แต่สูตร INDEX() กับ MATCH() ส่วนใหญ่ได้ด้วยสูตรเดียวที่เรียบง่าย

XLOOKUP() ค้นหาได้ทุกทิศทาง ดึงค่ากลับมาได้มากกว่าหนึ่งคอลัมน์ในครั้งเดียว และแสดงข้อความแบบกำหนดเองเมื่อไม่มีค่าที่ต้องการได้

ในคู่มือนี้ เราจะเริ่มจากไวยากรณ์ของฟังก์ชัน จากนั้นจะสร้างสูตรจริงทีละขั้นตอน โดยใช้ชุดข้อมูลเดียวตลอดทั้งเรื่อง นอกจากนี้ยังครอบคลุมการจับคู่แบบตรงตัวและประมาณค่า การดึงค่ามากกว่าหนึ่งคอลัมน์ การจัดการข้อผิดพลาด และการเปรียบเทียบระหว่าง XLOOKUP() กับ VLOOKUP() และ INDEX()/MATCH()

วิธีใช้ XLOOKUP() ใน Excel

มาสร้างสูตร XLOOKUP() จริงตั้งแต่เริ่มต้นกัน

ชุดข้อมูลของเรามีสินค้า 5 รายการ แต่ละรายการมีรหัส ชื่อ ประเภท และราคา อยู่ในคอลัมน์ A ถึง D โดยมีส่วนหัวอยู่ที่แถวที่ 1

ชุดข้อมูลสำหรับ XLOOKUP() ภาพโดยผู้เขียน

สมมติว่าอยากหาว่าราคา "P104" คือเท่าไร มาดูวิธีทำทีละขั้นตอน

ค้นหาค่าเดี่ยว

คลิกเซลล์ว่างที่ต้องการให้ผลลัพธ์แสดง ฉันใช้ F2 แล้วพิมพ์สูตรต่อไปนี้:

=XLOOKUP("P104", A2:A6, D2:D6)

ในสูตรนี้:

• "P104" คือ lookup_value หรือค่าที่เราค้นหา

• A2:A6 คือ lookup_array หรือช่วงที่ Excel ใช้ค้นหา

• D2:D6 คือ return_array หรือช่วงที่ Excel ดึงคำตอบกลับมา

Excel จะค้นหา "Product ID" ที่คอลัมน์ A หา "P104" เจอที่แถว 5 แล้วส่งค่าที่สอดคล้องจากคอลัมน์ D กลับมา: $45

แทนที่จะพิมพ์ "P104" ตรงๆ สามารถอ้างอิงจากเซลล์ได้ เพื่อให้เปลี่ยนค่าที่ค้นหาได้โดยไม่ต้องแก้สูตรเอง ในตัวอย่างนี้ เซลล์ A5 มี "P104" อยู่ ดังนั้นสูตรจึงกลายเป็น:

=XLOOKUP(A5, A2:A6, D2:D6)

ใช้ XLOOKUP() ใน Excel ภาพโดยผู้เขียน

ตอนนี้พิมพ์ Product ID ใดๆ ลงใน A5 ได้ แล้วราคาจะอัปเดตอัตโนมัติ

ดึงค่าจากคอลัมน์อื่น

ขั้นตอนนี้แสดงความแตกต่างที่แท้จริงระหว่าง XLOOKUP() กับ VLOOKUP() คุณไม่จำเป็นต้องถูกจำกัดให้ดึงค่าจากคอลัมน์ด้านขวาของคอลัมน์ที่ค้นหาเท่านั้น

สมมติว่าอยากได้ประเภทสินค้าแทนราคา สำหรับ "P102" การตั้งค่าเหมือนเดิม เพียงชี้ return_array ไปที่คอลัมน์ C "Category" แทน:

=XLOOKUP(A3, A2:A6, C2:C6)

ที่นี่ A3 จะคืนค่า Home หากเปลี่ยนเป็น A5 จะอัปเดตเป็น Electronics ในทำนองเดียวกัน เปลี่ยน return_array อีกครั้งเพื่อดึงชื่อสินค้า ราคา หรือคอลัมน์อื่นๆ ก็ได้ ทั้งหมดนี้ยังใช้ lookup_value และ lookup_array เดิม

XLOOKUP() คืนค่าจากคอลัมน์อื่น ภาพโดยผู้เขียน

ถ้าเป็น VLOOKUP() ต้องนับจำนวนคอลัมน์จากคอลัมน์ที่ค้นหา แล้วปรับหมายเลขดัชนีคอลัมน์ทุกครั้งที่ต้องการผลลัพธ์ต่างกัน แต่กับ XLOOKUP() เพียงชี้ไปยังคอลัมน์ที่ต้องการโดยตรง

คัดลอกสูตรลงหลายแถว

จนถึงตอนนี้ เราค้นหาครั้งละหนึ่งค่า แต่สเปรดชีตจริงส่วนใหญ่มักต้องค้นหาหลายค่าในคราวเดียว เช่น รายการรหัสคำสั่งซื้อทั้งชุดที่แต่ละรายการต้องการราคาที่ตรงกัน

วิธีตั้งค่าคือ: คอลัมน์ A ตั้งแต่ A2 มีรายการ Product ID ของเราอยู่แล้ว เราต้องการให้ราคาที่ตรงกันปรากฏถัดจากแต่ละรายการในคอลัมน์ F

ให้คลิก F2 แล้วพิมพ์สูตร โดยอ้างอิง A2 เป็นค่าที่ค้นหาแบบนี้:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

จากนั้นคลิก F2 อีกครั้ง แล้วลากตัวจัดการการเติม (สี่เหลี่ยมเล็กที่มุมล่างขวาของเซลล์) ลงไปตามแถวที่เหลือ

Excel จะคัดลอกสูตรลงมา และแต่ละแถวจะคืนราคารายการของตนอย่างถูกต้อง รหัสใน A2 ได้ราคาที่ F2 รหัสใน A3 ได้ราคาที่ F3 และต่อไปเรื่อยๆ

คัดลอกสูตรลงไปยังเซลล์อื่น ภาพโดยผู้เขียน

สังเกตเครื่องหมายดอลลาร์รอบ A2:A6 และ D2:D6 นี่เรียกว่าการอ้างอิงแบบสัมบูรณ์ ซึ่งล็อกช่วงข้อมูลไว้ไม่ให้ขยับเมื่อคัดลอกสูตรลงมา

ประเด็นนี้สำคัญ เพราะทุกแถวต้องค้นหารายการสินค้าเดียวกัน เพียงแต่มีค่าที่ค้นหาต่างกันในแต่ละครั้ง

การใช้ Exact Match และ Approximate Match ใน XLOOKUP()

โดยค่าเริ่มต้น XLOOKUP() จะค้นหาแบบตรงตัว หากไม่พบจะส่งกลับข้อผิดพลาด เว้นแต่จะตั้งค่าอาร์กิวเมนต์ if_not_found ไว้ตามที่กล่าวไปก่อนหน้านี้  

แต่ XLOOKUP() ก็สามารถหาค่าที่ใกล้เคียงที่สุดได้ โดยใช้อาร์กิวเมนต์ match_mode

การจับคู่แบบตรงตัว (ค่าเริ่มต้น)

ไม่ต้องพิมพ์อะไรเพิ่มเติม สูตรทั้งหมดที่สร้างมาก่อนหน้านี้ใช้การจับคู่แบบตรงตัวอยู่แล้ว เพราะเป็นพฤติกรรมเริ่มต้นของ XLOOKUP()

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel จะค้นหาค่าที่ตรงกับ A2 แบบเป๊ะๆ และจะคืนผลลัพธ์ก็ต่อเมื่อพบตรงกันในช่วงที่ค้นหาเท่านั้น

หากต้องการระบุให้ชัด ให้ใส่ศูนย์เป็นอาร์กิวเมนต์ตัวที่สี่:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() คืนค่าที่ตรงตัว ภาพโดยผู้เขียน

สังเกตช่องว่างก่อนเลข 0 นั่นคือการข้ามอาร์กิวเมนต์ if_not_found เพราะเราปล่อยว่างไว้ แล้วขยับไปที่ match_mode โดยตรง ส่วนใหญ่แล้วสามารถละอาร์กิวเมนต์นี้ไปเลย ปล่อยให้ Excel ใช้ค่าเริ่มต้นแบบตรงตัว

การจับคู่แบบประมาณค่า

คราวนี้ สมมติว่าข้อมูลของคุณไม่มีค่าครบทุกความเป็นไปได้ ให้นึกถึงโครงสร้างค่าคอมมิชชันที่พนักงานขายได้อัตราต่างกันตามช่วงยอดขายรวม จะไม่มีแถวสำหรับทุกจำนวนเงิน แต่จะมีเฉพาะจุดเริ่มของแต่ละช่วง

นี่คือจุดที่การจับคู่แบบประมาณค่ามีประโยชน์

ตั้งค่า match_mode เป็น -1 แล้ว XLOOKUP() จะหาค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับค่าที่ค้นหา

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

สมมติว่าช่วงที่ค้นหามีเกณฑ์ยอดขาย $0, $5,000 และ $10,000 แต่ละช่วงผูกกับอัตราคอมมิชชันต่างกัน

พนักงานที่มียอดขาย $7,500 จะไม่ตรงกับแถวใดเป๊ะๆ เมื่อ match_mode เป็น -1 XLOOKUP() จะหา $5,000 ซึ่งเป็นเกณฑ์ที่ใหญ่ที่สุดที่ไม่เกิน $7,500 แล้วคืนอัตราคอมมิชชันของแถวนั้น

ถ้าตั้ง match_mode เป็น 1 ตรรกะจะกลับกัน มันจะหาค่าที่น้อยที่สุดซึ่งมากกว่าหรือเท่ากับค่าที่ค้นหา เหมาะกับกรณีอย่างช่วงน้ำหนักจัดส่งที่ต้องปัดขึ้นไปยังขั้นถัดไปแทนการปัดลง

XLOOKUP() คืนค่าที่ใกล้เคียง ภาพโดยผู้เขียน

หมายเหตุ: การจับคู่แบบประมาณค่าถือว่าช่วงที่ค้นหาของคุณเรียงลำดับอยู่แล้ว เรียงจากน้อยไปมากสำหรับ -1 และจากมากไปน้อยสำหรับ 1 หากไม่ได้เรียงอย่างถูกต้อง XLOOKUP() อาจคืนค่าผิดโดยไม่แจ้งข้อผิดพลาด ซึ่งยิ่งแย่กว่า ดังนั้นควรตรวจสอบลำดับการเรียงก่อนใช้งานเสมอ

การค้นหาค่าทางซ้ายด้วย XLOOKUP()

VLOOKUP() จะค้นหาเฉพาะคอลัมน์ซ้ายสุดของช่วง และคืนค่าจากคอลัมน์ด้านขวาเท่านั้น ไม่สามารถทำกลับกันได้ อาร์กิวเมนต์ที่สามของมันไม่ใช่ช่วง แต่เป็นหมายเลขดัชนีคอลัมน์ที่นับจากขอบซ้าย

จึงไม่มีทางบอกมันว่า "ไปหนึ่งคอลัมน์ทางซ้าย" ได้ หากค่าที่ต้องการอยู่ทางซ้ายของคอลัมน์ที่ค้นหา ก็ต้องจัดเรียงข้อมูลใหม่หรือเพิ่มคอลัมน์ช่วยเพื่อให้ค้นหาได้

XLOOKUP() ไม่มีข้อจำกัดนี้ เพราะเราชี้ช่วงสำหรับค้นหาและสำหรับคืนค่าแยกกัน

กลับมาที่รายการสินค้า: Product ID อยู่คอลัมน์ A และ Product Name อยู่คอลัมน์ B

สมมติว่ามีชื่อสินค้าแล้วอยากหารหัสของมัน ซึ่งเป็นสิ่งที่ตรงข้ามกับสิ่งที่ VLOOKUP() ทำได้

ที่ C9 ให้กรอก Bluetooth Speaker (ชื่อที่มีอยู่ในรายการ) แล้วที่ E2 ใส่สูตร:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() จะค้นหาคำว่า "Bluetooth Speaker" ในคอลัมน์ B เจอที่แถว 5 แล้วส่งกลับ "P104" จากคอลัมน์ A แม้คอลัมน์ A จะอยู่ทางซ้ายของคอลัมน์ B ก็ตาม

XLOOKUP() ค้นหาค่าทางซ้ายได้ ซึ่ง VLOOKUP() ทำไม่ได้ ภาพโดยผู้เขียน

การคืนค่าหลายคอลัมน์ด้วย XLOOKUP()

XLOOKUP() สามารถคืนค่าหลายคอลัมน์พร้อมกันในสูตรเดียว โดยใช้ความสามารถที่เรียกว่า dynamic arrays วิธีทำคือชี้ return_array ไปยังช่วงที่กว้างกว่าหนึ่งคอลัมน์ แล้ว XLOOKUP() จะกระจายค่าทั้งแถวที่ตรงกันออกไปตามจำนวนเซลล์ที่ต้องการ

มาลองกับรายการสินค้าของเรา

ที่ C9 ฉันพิมพ์ Product ID เช่น "P104" แล้วที่ E2 ใส่สูตร:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

สังเกตว่า return_array คือ B2:D6 กว้าง 3 คอลัมน์ ครอบคลุม Product Name, Category และ Price เมื่อกด Enter XLOOKUP() จะหา "P104" ในคอลัมน์ A แล้วคืนค่า Bluetooth Speaker, Electronics และ $45 พร้อมกัน โดยกระจายไปยัง E9, F9 และ G9

XLOOKUP() คืนค่าหลายคอลัมน์ด้วยสูตรเดียว ภาพโดยผู้เขียน

นี่แหละที่เรียกว่า spill range E9 มีสูตรจริง ส่วน F9 และ G9 จะเติมค่าเองโดยไม่มีสูตรของตัวเอง

ลองคลิก F9 จะเห็นแถบสูตรแสดงสูตรต้นฉบับเป็นสีจาง ไม่ใช่สิ่งที่พิมพ์ลงไปโดยตรง หากลองพิมพ์ใน F9 ขณะที่มีการ spill จาก E9 อยู่ Excel จะขึ้นข้อผิดพลาดแบบ spill

เราจะอธิบายวิธีแก้ในส่วนข้อผิดพลาดที่พบบ่อย

การจัดการค่าที่หายไปใน XLOOKUP()

โดยค่าเริ่มต้น XLOOKUP() จะแสดงข้อผิดพลาด #N/A เมื่อหาไม่พบ อาร์กิวเมนต์ if_not_found ช่วยแก้โดยแสดงสิ่งที่เราต้องการแทนข้อผิดพลาดเริ่มต้นได้

สมมติว่าใน C9 ฉันพิมพ์ Product ID ที่ไม่มีในข้อมูล เช่น "P999"

การแสดงข้อความแบบกำหนดเอง

ที่ G2 ฉันใส่สูตร:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

เนื่องจาก "P999" ไม่มีในคอลัมน์ A XLOOKUP() จึงข้ามข้อผิดพลาดไปเลยและคืนค่า "Not Found" สามารถเปลี่ยนเป็นวลีที่ต้องการได้ เช่น "ตรวจสอบ Product ID" หรือ "No Match"

แสดงข้อความแบบกำหนดเองแทนข้อผิดพลาดเริ่มต้น ภาพโดยผู้เขียน

การแสดงค่าว่าง

บางครั้งต้องการให้เซลล์ว่างไม่มีข้อความ สำหรับกรณีนี้ให้ใช้เครื่องหมายอัญประกาศสองอันโดยไม่มีอะไรคั่นกลาง ดังนี้:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

เหมาะใช้เมื่อสูตรนี้อยู่ในรายงานที่ไม่ต้องการให้ข้อความ "Not Found" โผล่ปะปนข้างคอลัมน์ค่าจริงให้ดูรกตา

แสดงเซลล์ว่างแทนข้อผิดพลาดเริ่มต้น ภาพโดยผู้เขียน

ปล่อยให้เป็นข้อผิดพลาดจริง

หากไม่ใส่ if_not_found เลย XLOOKUP() จะกลับไปใช้ #N/A ซึ่งไม่ได้แย่เสมอไป หากสร้างสูตรใช้เองและอยากจับพิมพ์ผิดหรือค่าที่ขาดหายทันที การเห็น #N/A โผล่มาจะมีประโยชน์กว่าการปล่อยให้ว่างจนซ่อนปัญหา

หมายเหตุ: if_not_found จับได้เฉพาะกรณีหาไม่พบเท่านั้น ถ้า lookup_array และ return_array มีขนาดต่างกัน ตัวอย่างเช่น XLOOKUP() จะยังคงแจ้งข้อผิดพลาด #VALUE! และ if_not_found จะไม่ช่วยหยุดกรณีนี้ เราจะกล่าวถึงเคสนี้ในส่วนข้อผิดพลาดที่พบบ่อยด้านล่าง

ฟีเจอร์ขั้นสูงของ XLOOKUP()

มาดูฟีเจอร์ขั้นสูงของ XLOOKUP() กัน

ค้นหาจากล่างขึ้นบน

ตั้งค่า search_mode เป็น -1 แล้ว XLOOKUP() จะสแกนจากล่างขึ้นบน แทนการค้นหาจากบนลงล่าง ใช้เมื่อต้องเจอกับค่าซ้ำ และต้องการรายการล่าสุด ไม่ใช่รายการแรก

สมมติว่าในชุดข้อมูลของฉันมี Customer ID ซ้ำกันสามรายการ ในเซลล์ E2 ฉันใส่สูตรเริ่มต้นที่จะค้นหาจากบนลงล่าง:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

สูตรนี้คำนวณจากต้นตารางและแสดงผลจากค่าที่ตรงกันตัวแรก

แต่ถ้าอยากค้นหาจากด้านล่าง จะใช้สูตรนี้:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

ในสูตรนี้ -1 คือการบอก Excel ให้เริ่มค้นหาจากด้านล่างของชุดข้อมูล และสูตรนี้จะคืนค่า 340 เพราะนั่นคือค่าที่ตรงกันตัวแรกจากด้านล่าง

XLOOKUP() สแกนข้อมูลจากล่างขึ้นบน ภาพโดยผู้เขียน

โหมดการค้นหาแบบไบนารี

ตั้งค่า search_mode เป็น 2 แล้ว XLOOKUP() จะเปลี่ยนจากการตรวจทีละแถวเป็นการค้นหาแบบไบนารี

หลักการทำงานคือ:

• เริ่มดูรายการกึ่งกลางของช่วงก่อน

• ถ้าไม่ตรงกัน จะตัดครึ่งที่เป็นไปไม่ได้ทิ้ง แล้วดูตรงกลางของส่วนที่เหลือต่อ

• ทำซ้ำแบบนี้ โดยตัดข้อมูลครึ่งหนึ่งในแต่ละครั้ง จนกว่าจะเจอที่ตรงกัน

• หากตั้ง search_mode เป็น -2 จะทำงานเหมือนกัน เพียงแต่เริ่มจากรายการสุดท้ายแทนรายการแรก

ที่ E2 ให้ใส่:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

สูตรนี้กลับคืนค่า 200 แถวที่แตกต่างออกไป

โหมดค้นหาแบบไบนารีให้ผลลัพธ์ผิด ภาพโดยผู้เขียน

เพราะการค้นหาแบบไบนารีใช้ได้เฉพาะเมื่อ lookup_array เรียงลำดับอยู่แล้ว ในตัวอย่างคอลัมน์ Customer ID ของเราไม่ได้เรียง คือ C100, C101, C100, C102, C100 ทำให้การค้นหาแบบไบนารีตัดครึ่งผิดส่วนและให้ผลลัพธ์ผิด

ต่อไปมาดูว่าถ้าข้อมูลเรียงจริงๆ จะเกิดอะไรขึ้น

จัดเรียงบันทึกเดียวกันตาม Customer ID จากน้อยไปมาก (ให้ C100 ทั้งสามอยู่ต้นรายการ) แล้วรันสูตรเดิมอีกครั้ง

โหมดค้นหาแบบไบนารีใช้ได้เมื่อข้อมูลเรียงลำดับ ภาพโดยผู้เขียน

คราวนี้ E2 คืนค่า 120 ซึ่งตรงกับการค้นหาปกติ นี่คือความแตกต่าง การค้นหาแบบไบนารีจะข้ามข้อมูลที่ตัดทิ้งได้อย่างปลอดภัยเท่านั้น และเงื่อนไขนั้นจะเป็นจริงก็ต่อเมื่อข้อมูลถูกเรียงไว้ตั้งแต่แรก

สูตร XLOOKUP() แบบซ้อน

ซ้อน XLOOKUP() ตัวหนึ่งไว้ในอีกตัวหนึ่งเมื่อผลลัพธ์จากการค้นหาหนึ่งต้องนำไปใช้ในอีกการค้นหาหนึ่งแทนที่จะชี้ไปช่วงตายตัว

สมมติว่าต้องการหาราคา แต่คอลัมน์ที่จะดึงราคานั้นขึ้นอยู่กับการค้นหาแยกต่างหาก เช่น ระดับราคาของลูกค้า

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

ในสูตรนี้ XLOOKUP() ด้านในจะหาว่าควรดึงจากช่วงคอลัมน์ใดโดยอิงจาก D9 จากนั้น XLOOKUP() ด้านนอกใช้ผลลัพธ์นั้นเป็น return_array ของมัน

XLOOKUP() แบบซ้อน ภาพโดยผู้เขียน

XLOOKUP() เทียบกับ VLOOKUP()

เราได้อธิบายการทำงานของ XLOOKUP() อย่างละเอียดแล้ว ต่อไปคือการเปรียบเทียบกับ VLOOKUP()

มีสองประเด็นที่ควรกล่าวสั้นๆ:

• VLOOKUP() ตั้งค่าเริ่มต้นเป็นการจับคู่แบบประมาณค่า หากลืมใส่อาร์กิวเมนต์ที่สี่ จะไม่ขึ้นข้อผิดพลาด แต่จะคืนค่าผิดแบบเงียบๆ

• หากแทรกคอลัมน์ในชีตที่ใช้ VLOOKUP() สูตรจะเริ่มดึงค่าผิดโดยไม่เตือน XLOOKUP() หลีกเลี่ยงปัญหานี้ด้วยการอ้างอิงช่วงโดยตรงแทนการนับคอลัมน์

ทั้งหมดนี้ไม่ได้ทำให้ VLOOKUP() ล้าสมัย มันยังใช้ได้ดีกับชีตแบบง่ายและนิ่ง แต่สำหรับงานที่สร้างใหม่ต่อจากนี้ XLOOKUP() คือทางเลือกมาตรฐานที่ปลอดภัยกว่า

XLOOKUP() เทียบกับ INDEX() และ MATCH()

ก่อนที่ XLOOKUP() จะมีอยู่ INDEX() และ MATCH() ร่วมกันเป็นวิธีมาตรฐานในการเลี่ยงข้อจำกัดของ VLOOKUP() โดยใช้สองฟังก์ชันแทนฟังก์ชันเดียว

• MATCH() หาตำแหน่งของค่าที่ค้นหา

• INDEX() ใช้ตำแหน่งนั้นเพื่อดึงผลลัพธ์จากที่ใดก็ได้ที่ชี้ไว้ ซ้ายหรือขวาก็ได้

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

ลองเทียบกับเวอร์ชัน XLOOKUP() ที่เราใช้ตลอดบทความนี้:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() ใช้งานได้เฉพาะใน Microsoft 365 และ Excel 2021 ขึ้นไปตามที่กล่าวไป หากเวิร์กบุ๊กต้องเปิดใน Excel 2019 หรือ 2016 หรือแชร์กับผู้ใช้เวอร์ชันเก่า INDEX() และ MATCH() ยังใช้ได้ทุกที่

ยังมีกรณีเล็กๆ อีกกรณีหนึ่ง

INDEX() และ MATCH() แยกขั้น "หาตำแหน่ง" ออกจาก "คืนค่า" ซึ่งช่วยได้ในสูตรซ้อนที่ซับซ้อนบางแบบ กรณีนี้พบไม่บ่อย นักอ่านส่วนใหญ่ไม่จำเป็นต้องใช้

นอกเหนือจากสองกรณีนี้ XLOOKUP() เป็นตัวเลือกที่ดีกว่าสำหรับเวิร์กบุ๊กใหม่

ข้อผิดพลาด XLOOKUP() ที่พบบ่อยและวิธีแก้

นี่คือข้อมูลอ้างอิงรวดเร็วสำหรับปัญหาที่มักพบและวิธีแก้แต่ละข้อ

ข้อผิดพลาด #N/A

นี่คือการตอบสนองเริ่มต้นของ XLOOKUP() เมื่อหาไม่พบ ฉันเคยเจอไปแล้วก่อนหน้านี้ ตอนที่ lookup_value กับ lookup_array ไม่สอดคล้องกัน และ XLOOKUP() ไม่มีสิ่งใดให้จับคู่ในคอลัมน์ A

วิธีแก้: ตรวจสอบให้แน่ใจว่า lookup_value มีอยู่จริงใน lookup_array และสะกด/จัดรูปแบบตรงกัน หากคาดว่าจะมีการไม่พบเป็นเรื่องปกติ ให้ใช้อาร์กิวเมนต์ if_not_found เพื่อแทนที่ #N/A ด้วยสิ่งที่มีประโยชน์กว่า เช่น "Not Found" หรือเซลล์ว่าง ตามที่กล่าวไปก่อนหน้านี้

ช่วงที่ค้นหาผิด

เกิดขึ้นเมื่อ lookup_array และ return_array ไม่สอดคล้องกัน: แถวผิด คอลัมน์ผิด หรือช่วงมีขนาดไม่ตรงกัน

ขึ้นกับเวอร์ชัน Excel หรือ Sheets ที่ใช้ ปัญหานี้อาจแสดงเป็นข้อผิดพลาด #VALUE! หรือบางครั้งเป็น #N/A พร้อมข้อความอธิบายปัญหาที่แท้จริง อย่าปล่อยให้ #N/A หลอกให้คิดว่าเป็นการหาไม่พบ

วิธีแก้: ให้แน่ใจว่า lookup_array และ return_array ครอบคลุมจำนวนแถวหรือคอลัมน์เท่ากัน หาก lookup_array คือ A2:A5 return_array ก็ต้องครอบคลุมแถว 2 ถึง 5 เช่นกัน ไม่ใช่แถว 4 ถึง 5 หรือช่วงที่ไม่ตรงกันใดๆ

ชนิดข้อมูลไม่ตรงกัน

VLOOKUP() อาจแจ้งข้อผิดพลาดเพราะคาดว่าเป็นตัวเลขแต่กลับเป็นข้อความ XLOOKUP() ก็เจอปัญหาแบบเดียวกันได้ หาก lookup_value เป็นตัวเลข แต่ lookup_array เก็บค่าเดียวกันเป็นข้อความ หรือกลับกัน XLOOKUP() จะหาไม่เจอ แม้ค่าบนหน้าจอจะดูเหมือนกัน

วิธีแก้: ตรวจสอบว่า lookup_value และ lookup_array ใช้ชนิดข้อมูลเดียวกัน ตัวเลขที่เก็บเป็นข้อความมักเรียงชิดซ้ายในเซลล์แทนที่จะชิดขวา ครอบ lookup_value ด้วย VALUE() เพื่อแปลงข้อความเป็นตัวเลข หรือใช้ TEXT() เพื่อทำกลับกัน ขึ้นอยู่กับทิศทางของความไม่ตรงกัน

ข้อผิดพลาดแบบ spill

เกิดขึ้นเมื่อ return_array กว้างกว่าหนึ่งคอลัมน์ แต่เซลล์ที่ XLOOKUP() ต้องการใช้ไม่ว่าง Excel ต้องการพื้นที่ว่างข้างๆ เซลล์สูตรเพื่อกระจายผลลัพธ์ หากมีสิ่งใดอยู่ก่อนแล้ว สูตรทั้งหมดจะล้มเหลว

วิธีแก้: เคลียร์เซลล์ที่ผลลัพธ์ต้องไปอยู่ให้ว่าง แล้วค่อยใส่สูตร หากจำเป็นต้องมีข้อมูลในเซลล์เหล่านั้น ให้ย้ายสูตร XLOOKUP() ไปที่อื่นที่มีพื้นที่ว่างรอบๆ

บทส่งท้าย

ตอนนี้คุณรู้วิธีใช้ XLOOKUP() สำหรับค่าทีละรายการ หลายคอลัมน์ ข้อมูลที่หายไป และการค้นหาแบบซ้อนข้ามสองช่วงแล้ว ครอบคลุมไปมากพอสมควร

ต่อจากนี้ให้ใช้ XLOOKUP() เป็นค่าเริ่มต้นสำหรับเวิร์กบุ๊กใหม่ มันยืดหยุ่นกว่า VLOOKUP() อ่านง่ายกว่า INDEX/MATCH() และช่วยให้ไม่ต้องหุ้มทุกสูตรด้วย IFERROR()

หากอยากลงลึกขึ้น ก็ยังคุ้มค่าที่จะเรียนรู้ INDEX() และ MATCH() แยกกันด้วย