ฟังก์ชัน 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 เรียงลําดับจากมากไปหาน้อย ถ้าไม่ได้เรียงลําดับ ผลลัพธ์ที่ไม่ถูกต้องจะถูกส่งกลับ

อาร์กิวเมนต์

คำอธิบาย

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 ใช้อาร์เรย์การค้นหาและอาร์เรย์ที่ส่งกลับ ในขณะที่ 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 (เรียงตามประเภท)

ไวยากรณ์

ตัวอย่าง

ดูเพิ่มเติม

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

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

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!