ฟังก์ชัน XLOOKUP

ใช้ฟังก์ชัน XLOOKUP เพื่อค้นหาสิ่งต่างๆ ในตารางหรือช่วงตามแถว ตัวอย่างเช่น หาราคาของชิ้นส่วนอะไหล่รถยนต์ตามหมายเลขชิ้นส่วน หรือค้นหาชื่อพนักงานตาม ID พนักงานของชิ้นส่วน ด้วย XLOOKUP คุณสามารถค้นหาคําค้นหาในคอลัมน์หนึ่ง และส่งกลับผลลัพธ์จากแถวเดียวกันในอีกคอลัมน์หนึ่งได้ โดยไม่คํานึงว่าคอลัมน์ที่ส่งกลับจะอยู่ด้านใด

ไวยากรณ์

ฟังก์ชัน XLOOKUP จะค้นหาช่วงหรืออาร์เรย์ แล้วส่งกลับรายการที่สอดคล้องกับค่าที่ตรงกันค่าแรกที่พบ ถ้าไม่มีค่าที่ตรงกัน XLOOKUP สามารถส่งกลับค่าที่ตรงกันที่ใกล้เคียงที่สุด (โดยประมาณ) 

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

อาร์กิวเมนต์	คำอธิบาย
lookup_value จําเป็น*	ค่าที่จะค้นหา *ถ้าละไว้ XLOOKUP จะส่งกลับเซลล์ว่างที่พบใน lookup_array
lookup_array จำเป็น	อาร์เรย์หรือช่วงที่จะค้นหา
return_array จำเป็น	อาร์เรย์หรือช่วงที่จะส่งกลับ
[if_not_found] ไม่จำเป็น	เมื่อไม่พบรายการที่ตรงกันที่ถูกต้อง ให้ส่งกลับข้อความ [if_not_found] ที่คุณระบุ ถ้าไม่พบรายการที่ตรงกันที่ถูกต้องและขาดหายไป [ if_not_found] #N/A จะถูกส่งกลับ
[match_mode] ไม่จำเป็น	ระบุชนิดที่ตรงกัน: 0 - ตรงกันทุกประการ หากไม่พบ ให้ส่งกลับ #N/A นี่คือค่าเริ่มต้น -1 - ตรงกันพอดี ถ้าไม่พบ ให้ส่งกลับรายการที่เล็กกว่าถัดไป 1 - ค่าที่ตรงกันพอดี ถ้าไม่พบ ให้ส่งกลับรายการถัดไปที่ใหญ่กว่า 2 - การจับคู่อักขระตัวแทน ซึ่ง *, ? และ ~ มีความหมายพิเศษ
[search_mode] ไม่จำเป็น	ระบุโหมดการค้นหาที่จะใช้: 1 - ดําเนินการค้นหาโดยเริ่มต้นที่รายการแรก นี่คือค่าเริ่มต้น -1 - ดําเนินการค้นหาย้อนกลับโดยเริ่มจากข้อมูลสุดท้าย 2 - ดําเนินการค้นหาแบบไบนารีที่ขึ้นอยู่กับ lookup_array เรียงลําดับจากน้อยไปหามาก ถ้าไม่ได้เรียงลําดับ จะส่งกลับผลลัพธ์ที่ไม่ถูกต้อง -2 - ดําเนินการค้นหาแบบไบนารี lookup_array เรียงลําดับจากมากไปหาน้อย ถ้าไม่ได้เรียงลําดับ จะส่งกลับผลลัพธ์ที่ไม่ถูกต้อง

ตัวอย่าง

ตัวอย่างที่ 1 ใช้ XLOOKUP เพื่อค้นหาชื่อประเทศในช่วง แล้วส่งกลับรหัสประเทศของโทรศัพท์ ซึ่งรวมถึงอาร์กิวเมนต์ lookup_value (เซลล์ F2), lookup_array (ช่วง B2:B11) และ return_array (ช่วง D2:D11) ซึ่งจะไม่มีอาร์กิวเมนต์ match_mode เนื่องจาก XLOOKUP สร้างค่าที่ตรงกันพอดีตามค่าเริ่มต้น

———————————————————————————

ตัวอย่างที่ 2 ค้นหาข้อมูลพนักงานโดยยึดตามหมายเลข ID พนักงาน XLOOKUP สามารถส่งกลับอาร์เรย์ที่มีหลายรายการได้ ซึ่งต่างจาก VLOOKUP ดังนั้นสูตรเดียวจึงสามารถส่งกลับทั้งชื่อพนักงานและแผนกจากเซลล์ C5:D14

———————————————————————————

ตัวอย่างที่ 3 เพิ่มอาร์กิวเมนต์ if_not_found ไปยังตัวอย่างก่อนหน้า

———————————————————————————

ตัวอย่างที่ 4 จะค้นหาในคอลัมน์ C สําหรับรายได้ส่วนบุคคลที่ใส่ในเซลล์ E2 และพบอัตราภาษีที่ตรงกันในคอลัมน์ B ซึ่งจะตั้งค่าอาร์กิวเมนต์ if_not_found เพื่อส่งกลับ 0 (ศูนย์) ถ้าไม่พบอะไรเลย อาร์กิวเมนต์ match_mode ถูกตั้งค่าเป็น 1ซึ่งหมายความว่าฟังก์ชันจะค้นหาค่าที่ตรงกัน และถ้าไม่พบ ฟังก์ชันจะส่งกลับรายการถัดไปที่มีขนาดใหญ่กว่า สุดท้าย อาร์กิวเมนต์ search_mode ถูกตั้งค่าเป็น 1 ซึ่งหมายความว่าฟังก์ชันจะค้นหาจากรายการแรกจนถึงรายการสุดท้าย

———————————————————————————

ตัวอย่างที่ 5 ใช้ฟังก์ชัน XLOOKUP ที่ซ้อนกันเพื่อดําเนินการจับคู่ทั้งแนวตั้งและแนวนอน ก่อนอื่นจะค้นหา กําไรขั้นต้น ในคอลัมน์ B จากนั้นค้นหา Qtr1 ในแถวบนสุดของตาราง (ช่วง C5:F5) และสุดท้ายจะส่งกลับค่าที่จุดตัดของทั้งสอง ซึ่งจะคล้ายกับการใช้ฟังก์ชัน INDEX และ MATCH ร่วมกัน

———————————————————————————

ตัวอย่างที่ 6 ใช้ ฟังก์ชัน SUM และฟังก์ชัน XLOOKUP ที่ซ้อนกันสองตัวเพื่อหาผลรวมของค่าทั้งหมดระหว่างสองช่วง ในกรณีนี้เราต้องการรวมค่าสําหรับองุ่นกล้วยและรวมถึงลูกแพร์ซึ่งอยู่ระหว่างทั้งสอง

สูตรในเซลล์ E3 คือ: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

วิธีการใช้งาน XLOOKUP จะส่งกลับช่วง ดังนั้นเมื่อทําการคํานวณแล้ว สูตรจะแสดงผลเป็นดังนี้: =SUM($E$7:$E$9) คุณสามารถดูวิธีการทํางานนี้ได้ด้วยตัวคุณเองโดยเลือกเซลล์ที่มีสูตร XLOOKUP ที่คล้ายกับสูตรนี้ จากนั้นเลือก สูตร ตรวจสอบ>สูตร>ประเมินสูตร จากนั้นเลือก ประเมิน เพื่อทําการคํานวณตามขั้นตอน 

———————————————————————————