สูตรและฟังก์ชัน

XLOOKUP

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

หมายเหตุ: XLOOKUP ไม่พร้อมใช้งานใน Excel 2016 และ Excel 2019 อย่างไรก็ตาม คุณอาจพบสถานการณ์ในการใช้เวิร์กบุ๊กใน Excel 2016 หรือ Excel 2019 ที่มีฟังก์ชัน XLOOKUP ที่สร้างขึ้นโดยบุคคลอื่นที่ใช้ Excel เวอร์ชันที่ใหม่กว่า

เบราว์เซอร์ของคุณไม่สนับสนุนวิดีโอ ติดตั้ง Microsoft Silverlight, Adobe Flash Player หรือ Internet Explorer 9

ไวยากรณ์

ฟังก์ชัน 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 สร้างค่าที่ตรงกันทุกประการตามค่าเริ่มต้น

ตัวอย่างของฟังก์ชัน XLOOKUP ที่ใช้ในการส่งกลับชื่อพนักงานและแผนกตามรหัสพนักงาน สูตรคือ =XLOOKUP(B2,B5:B14,C5:C14)

หมายเหตุ: XLOOKUP ใช้อาร์เรย์การค้นหาและอาร์เรย์ที่ส่งกลับ ในขณะที่ VLOOKUP ใช้อาร์เรย์ตารางเดี่ยวตามด้วยหมายเลขดัชนีคอลัมน์ สูตร VLOOKUP ที่เทียบเท่าในกรณีนี้จะเป็น: =VLOOKUP(F2,B2:D11,3,FALSE)

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

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

ตัวอย่างของฟังก์ชัน XLOOKUP ที่ใช้ในการส่งกลับชื่อพนักงานและแผนกตามรหัสพนักงาน สูตรคือ: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

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

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

ตัวอย่างของฟังก์ชัน XLOOKUP ที่ใช้ในการส่งกลับชื่อพนักงานและแผนกโดยยึดตาม ID พนักงานที่มีอาร์กิวเมนต์ if_not_found สูตรคือ =XLOOKUP(B2,B5:B14,C5:D14,0,1,"ไม่พบพนักงาน")

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

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

รูปภาพของฟังก์ชัน XLOOKUP ที่ใช้เพื่อส่งกลับอัตราภาษีตามรายได้สูงสุด ค่านี้ใกล้เคียงกัน สูตรคือ: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

หมายเหตุ: คอลัมน์ lookup_array ของ XARRAY อยู่ทางด้านขวาของคอลัมน์ return_array ในขณะที่ VLOOKUP สามารถดูได้จากซ้ายไปขวาเท่านั้น

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

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

เคล็ดลับ: คุณยังสามารถใช้ XLOOKUP เพื่อแทนที่ฟังก์ชัน HLOOKUP ได้

รูปภาพของฟังก์ชัน XLOOKUP ที่ใช้เพื่อส่งกลับข้อมูลแนวนอนจากตารางโดยการซ้อน 2 XLOOKUP สูตรคือ: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

หมายเหตุ: สูตรในเซลล์ D3:F3 คือ: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

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

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

การใช้ XLOOKUP กับ SUM เพื่อหาผลรวมช่วงของค่าที่อยู่ระหว่างสองส่วนที่เลือก

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

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

หมายเหตุ: ขอบคุณ Microsoft Excel MVP, Bill Jelen ที่เสนอตัวอย่างนี้

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

ดูเพิ่มเติม

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community หรือรับการสนับสนุนใน ชุมชน

ฟังก์ชัน XMATCH

ฟังก์ชันของ Excel (เรียงลำดับตามตัวอักษร)

ฟังก์ชันของ Excel (เรียงตามประเภท)

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ต้องการตัวเลือกเพิ่มเติมหรือไม่

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย