ค้นพบว่าเหตุใด XLOOKUP จึงเป็นโซลูชันการค้นหา Excel ขั้นสูงสุด

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

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

ไวยากรณ์:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

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

เหตุผลที่ 1:XLOOKUP สามารถมองไปในทิศทางใดก็ได้ (ซ้ายหรือขวา)

VLOOKUP สามารถค้นหาได้ทางด้านขวาของคอลัมน์การค้นหาของคุณเท่านั้น ถ้าคอลัมน์การค้นหาไม่ใช่คอลัมน์แรกในอาร์เรย์ตารางของคุณ สูตรจะล้มเหลว INDEX/MATCH แก้ปัญหานี้ได้แต่อ่านยากกว่าเพราะต้องใช้ฟังก์ชัน 2 ซ้อนเข้าด้วยกัน ซึ่งอาจทำให้ชุดข้อมูลขนาดใหญ่ยุ่งเหยิงได้

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

สมมติว่าคุณมีชุดข้อมูลการขายและต้องการค้นหารหัสผลิตภัณฑ์โดยการค้นหาชื่อผลิตภัณฑ์

ดู VLOOKUP:

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=VLOOKUP(H5,A2:F23,1,FALSE)

สูตรนี้ส่งคืน 07 ข้อผิดพลาดเนื่องจาก VLOOKUP ไม่สามารถมองไปทางซ้ายได้ (รหัสผลิตภัณฑ์ปรากฏก่อนชื่อผลิตภัณฑ์ในแผ่นงาน) หากต้องการใช้ VLOOKUP ที่นี่ คุณจะต้องจัดเรียงคอลัมน์ใหม่ ซึ่งมักจะไม่สามารถทำได้เนื่องจากอาจทำให้โครงสร้างข้อมูลและสูตรดาวน์สตรีมเสียหายได้

ดัชนี/การจับคู่:

• เลือกเซลล์และใช้คำสั่งผสม INDEX/MATCH

=INDEX(A2:A23, MATCH(H8, B2:B23, 0))

สูตรนี้ส่งคืนรหัสผลิตภัณฑ์ (P006) ใช้งานได้ แต่คุณต้องระบุทั้งสองช่วงและตรวจสอบให้แน่ใจว่า 11 ถูกต้อง (24 ) ซึ่งเพิ่มการใช้คำฟุ่มเฟือย

เอ็กซ์ลุคอัพ:
XLOOKUP ไม่สนใจลำดับคอลัมน์ คุณเพียงแค่บอกว่าจะต้องค้นหาอะไรและจะส่งคืนค่าจากที่ไหน

• เลือกเซลล์และแทรกสูตร XLOOKUP แบบไดนามิก

=XLOOKUP(H12, B2:B23, A2:A23)

สูตรนี้ค้นหาชื่อผลิตภัณฑ์และส่งกลับรหัสผลิตภัณฑ์ที่สอดคล้องกัน (P006) แม้ว่ารหัสจะอยู่ทางซ้ายก็ตาม ไวยากรณ์ที่ชัดเจนยิ่งขึ้น ไม่จำเป็นต้องมีการซ้อน

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

=XLOOKUP(H12, B2:B23, A2:E23)

ซึ่งจะส่งคืนหลายคอลัมน์โดย "กระจาย" ในเซลล์ที่อยู่ติดกัน ดังนั้นคุณจึงได้รับข้อมูลผลิตภัณฑ์ทั้งหมดในขั้นตอนเดียว

เหตุผลที่ 2:การจัดการข้อผิดพลาดในตัว (ไม่มี #N/A อีกต่อไป)

เมื่อไม่พบค่าการค้นหา Excel จะส่งกลับ 36 . เมื่อใช้ VLOOKUP หรือ INDEX/MATCH โดยทั่วไปคุณจะล้อมสูตรด้วย 43 เพื่อจัดการสิ่งนี้อย่างสง่างาม โดยเพิ่มการซ้อนเพิ่มเติม

XLOOKUP มี if_not_found ในตัว อาร์กิวเมนต์ เพื่อให้คุณสามารถระบุข้อความที่เป็นมิตรหรือค่าทางเลือกได้ในสูตร

มาค้นหาราคาต่อหน่วยผลิตภัณฑ์ที่อาจไม่มีอยู่ในฐานข้อมูลกัน (เช่น "ที่ชาร์จไร้สาย")

VLOOKUP + ตัวอ้างอิง:

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")

วิธีนี้ได้ผล แต่มีรายละเอียดมากและเพิ่มการซ้อนอีกชั้นหนึ่ง

ดัชนี/การจับคู่ + ตัวอ้างอิง:

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=IFERROR(INDEX(D2:D23, MATCH(H8, B2:B23, 0)), "Not Found")

ได้ผลอีกครั้ง แต่ wrapper พิเศษจะทำให้สูตรยาวขึ้น

เอ็กซ์ลุคอัพ:
56 ของ XLOOKUP อาร์กิวเมนต์จัดการค่าที่หายไปอย่างหรูหรา

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=XLOOKUP(H12, B2:B23, D2:D23, "Not Found")

สูตรนี้ค้นหาชื่อผลิตภัณฑ์ หากไม่พบ ระบบจะส่งกลับ "ไม่พบ" กระชับและหลีกเลี่ยงฟังก์ชันพิเศษ

เหตุผลที่ 3:การค้นหาสัญลักษณ์ตัวแทนทำได้ง่าย

VLOOKUP รองรับไวด์การ์ด (63 และ 74 ) ในโหมดการทำงานแบบตรงทั้งหมด (89 ) สำหรับการค้นหาข้อความ แต่ไวยากรณ์อาจไม่ชัดเจนเสมอไป INDEX/MATCH ยังรองรับไวด์การ์ดเพราะ 97 จดจำได้เมื่อ 106 คือ 115 . XLOOKUP ทำให้เจตนาชัดเจนด้วย match_mode จาก 129 สำหรับการจับคู่ไวด์การ์ด ซึ่งช่วยเพิ่มความชัดเจนและลดความประหลาดใจ

=VLOOKUP("Lap*", B2:D23, 3, FALSE)

=INDEX(D2:D23, MATCH("Lap*", B2:B23, 0))

=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)