วิธีใช้ฟังก์ชัน XLOOKUP ใหม่และขั้นสูงใน Excel (10 ตัวอย่าง)
เอ็กเซล ใหม่ล่าสุด เอ็กซ์ลุคอัพ เป็นฟังก์ชันการค้นหาที่มีประสิทธิภาพและง่ายที่สุดที่ Excel สามารถนำเสนอได้ ด้วยความพยายามอย่างไม่ลดละ ในที่สุด Microsoft ก็ปล่อยฟังก์ชัน XLOOKUP นี้เพื่อแทนที่ VLOOKUP, HLOOKUP, INDEX+MATCH และฟังก์ชันการค้นหาอื่นๆ
ในบทแนะนำนี้เราจะแสดงให้คุณเห็น ข้อดีของ XLOOKUP คืออะไร และ มาสมัครกันได้ยังไง เพื่อแก้ปัญหาการค้นหาต่างๆ
ดาวน์โหลด XLOOKUP ไฟล์ตัวอย่าง
จะรับ XLOOKUP ได้อย่างไร?
ตั้งแต่ ฟังก์ชัน XLOOKUP is ใช้ได้เฉพาะ in Excel สำหรับ Microsoft 365, 2021 Excelและ Excel สำหรับเว็บคุณสามารถอัปเกรด Excel เป็นเวอร์ชันที่พร้อมใช้งานเพื่อรับ XLOOKUP
XLOOKUP ไวยากรณ์ของฟังก์ชัน
ฟังก์ชัน XLOOKUP ค้นหาช่วงหรืออาร์เรย์แล้วคืนค่าของผลลัพธ์ที่ตรงกันครั้งแรก. วากยสัมพันธ์ ของฟังก์ชัน XLOOKUP มีดังนี้:
ข้อโต้แย้ง:
- Lookup_value (จำเป็น): ความคุ้มค่าที่คุณต้องการ สามารถอยู่ในคอลัมน์ใดๆ ของช่วง table_array
- lookup_array (จำเป็น): อาร์เรย์หรือช่วงที่คุณค้นหาค่าการค้นหา
- Return_array (จำเป็น): อาร์เรย์หรือช่วงจากตำแหน่งที่คุณต้องการรับค่า
- If_not_found (ไม่บังคับ): ค่าที่จะส่งคืนเมื่อไม่พบการจับคู่ที่ถูกต้อง คุณสามารถปรับแต่งข้อความใน [if_not_found] เพื่อแสดงว่าไม่มีรายการที่ตรงกัน
มิฉะนั้น ค่าที่ส่งคืนจะเป็น #N/A โดยค่าเริ่มต้น - Match_mode (ไม่บังคับ): ที่นี่ คุณสามารถระบุวิธีจับคู่ lookup_value กับค่าใน lookup_array
- 0 (ค่าเริ่มต้น) = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้ส่งคืน #N/A
- -1 = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้คืนค่าที่น้อยกว่าถัดไป
- 1 = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้คืนค่าที่มากกว่าถัดไป
- 2 = การแข่งขันบางส่วน ใช้อักขระตัวแทนเช่น *, ? และ ~ เพื่อเรียกใช้การจับคู่ไวด์การ์ด
- Search_mode (ไม่บังคับ): ที่นี่ คุณสามารถระบุลำดับการค้นหาที่จะดำเนินการได้
- 1 (ค่าเริ่มต้น) = ค้นหา lookup_value จากรายการแรกไปยังรายการสุดท้ายใน lookup_array
- -1 = ค้นหา lookup_value จากรายการสุดท้ายไปยังรายการแรก ช่วยเมื่อคุณต้องการรับผลลัพธ์ที่ตรงกันล่าสุดใน lookup_array
- 2 = ทำการค้นหาแบบไบนารีที่ต้องการ lookup_array ที่เรียงลำดับจากน้อยไปมาก หากไม่เรียงลำดับ ผลการส่งคืนจะไม่ถูกต้อง
- -2 = ดำเนินการค้นหาแบบไบนารีที่ต้องการ lookup_array ที่เรียงลำดับจากมากไปน้อย หากไม่เรียงลำดับ ผลการส่งคืนจะไม่ถูกต้อง
สำหรับ ข้อมูลโดยละเอียดเกี่ยวกับอาร์กิวเมนต์ของฟังก์ชัน XLOOKUPโปรดดำเนินการดังนี้:
1. พิมพ์ ด้านล่างไวยากรณ์ ลงในเซลล์ว่าง โปรดทราบว่าคุณต้องพิมพ์วงเล็บด้านเดียวเท่านั้น
2 กด Ctrl + Aแล้ว กล่องข้อความ ปรากฏขึ้นซึ่งแสดงให้เห็น อาร์กิวเมนต์ฟังก์ชัน. และอีกด้านของโครงยึดจะเสร็จสิ้นโดยอัตโนมัติ
3. ดึงแผงข้อมูลลงแล้วคุณจะมองเห็นทั้งหมด อาร์กิวเมนต์หกฟังก์ชัน ของ XLOOKUP
![]() |
>>> | ![]() |
ตัวอย่างฟังก์ชัน XLOOKUP
ฉันแน่ใจว่าคุณเข้าใจหลักการพื้นฐานของฟังก์ชัน XLOOKUP แล้ว มาดำดิ่งลงไปใน ตัวอย่างการปฏิบัติ ของ XLOOKUP
ตัวอย่างที่ 1: การจับคู่แบบตรงทั้งหมด
ทำการจับคู่แบบตรงทั้งหมดด้วย XLOOKUP
คุณเคยหงุดหงิดเพราะต้องระบุโหมดการจับคู่แบบตรงทั้งหมดทุกครั้งที่ใช้ VLOOKUP หรือไม่? โชคดีที่ปัญหานี้ไม่มีอีกต่อไปเมื่อคุณลองใช้ฟังก์ชัน XLOOKUP อันน่าทึ่ง โดยค่าเริ่มต้น XLOOKUP จะสร้างการจับคู่แบบตรงทั้งหมด.
ตอนนี้ สมมติว่าคุณมีรายการสินค้าคงคลังของเครื่องใช้สำนักงาน และคุณจำเป็นต้องทราบราคารวมของรายการหนึ่ง เช่น เมาส์ โปรดทำดังนี้
พิมพ์ ด้านล่างสูตร ลงในเซลล์ว่าง F2 แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP(E2,A2:A10,C2:C10)
ตอนนี้คุณรู้ราคาต่อหน่วยของเมาส์แล้วด้วยสูตร XLOOKUP ขั้นสูง เนื่องจากรหัสการจับคู่ได้ตั้งค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด คุณไม่จำเป็นต้องระบุรหัสนั้น ง่ายกว่าและมีประสิทธิภาพมากกว่า VLOOKUP
เพียงไม่กี่คลิกเพื่อรับการจับคู่ที่แน่นอน
บางทีคุณกำลังใช้ Excel เวอร์ชันที่ต่ำกว่าและยังไม่มีแผนที่จะอัปเกรดเป็น Excel 2021 หรือ Microsoft 365 ในกรณีนี้ฉันจะแนะนำ คุณสมบัติที่มีประโยชน์ - ค้นหาค่าในสูตรรายการ of Kutools for Excel. ด้วยคุณสมบัตินี้ คุณสามารถรับผลลัพธ์โดยไม่ต้องใช้สูตรที่ซับซ้อนหรือเข้าถึง XLOOKUP
ด้วย our โปรแกรมเสริม Excel สถาบันทั้งหมดโปรดทำดังนี้:
1. คลิกเซลล์เพื่อใส่ผลลัพธ์ที่ตรงกัน
2 ไปที่ Kutools คลิกแท็บ ตัวช่วยสูตรแลวคลิกปุ the ม ตัวช่วยสูตรในรายการแบบหล่นลง.
3 ใน กล่องโต้ตอบตัวช่วยสูตรโปรดกำหนดค่าดังนี้:
- เลือก ค้นหา ใน ส่วนประเภทสูตร;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร เลือกส่วนสูตรให้เลือก มองหาค่าในรายการ;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนอินพุตอาร์กิวเมนต์โปรดดำเนินการดังนี้:
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร Table_array กล่องเลือกช่วงข้อมูลที่มีค่าการค้นหาและค่าผลลัพธ์
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร lookup_value กล่องให้เลือกเซลล์หรือช่วงของค่าที่คุณกำลังค้นหา โปรดทราบว่าต้องอยู่ในคอลัมน์แรกของ table_array
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร กล่องคอลัมน์เลือกคอลัมน์ที่คุณจะคืนค่าที่ตรงกัน
4 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์
ตัวอย่างที่ 2 การจับคู่โดยประมาณ
ทำการแข่งขันโดยประมาณด้วย XLOOKUP
ที่จะเรียกใช้ an การค้นหาโดยประมาณคุณจะต้อง ตั้งค่าโหมดการจับคู่เป็น 1 หรือ -1 ในอาร์กิวเมนต์ที่ห้า. เมื่อไม่พบการจับคู่แบบตรงทั้งหมด จะส่งคืนค่าที่มากกว่าหรือน้อยกว่าถัดไป.
ในกรณีนี้ คุณจำเป็นต้องทราบอัตราภาษีของรายได้ของพนักงานของคุณ ทางด้านซ้ายของสเปรดชีตคือ Federal Income Tax Brackets สำหรับปี 2021 คุณจะรับอัตราภาษีของพนักงานในคอลัมน์ E ได้อย่างไร ไม่ต้องกังวล โปรดดำเนินการดังนี้:
1. พิมพ์ ด้านล่างสูตร ลงในเซลล์ว่าง E2 แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
จากนั้นเปลี่ยนการจัดรูปแบบของผลลัพธ์ที่ส่งคืนตามที่คุณต้องการ
=XLOOKUP(D2,B2:B8,A2:A8,,1)
![]() |
>>> | ![]() |
√ หมายเหตุ: อาร์กิวเมนต์ที่สี่ [If_not_found] เป็นทางเลือก ฉันก็เลยข้ามไป
2. ตอนนี้คุณรู้อัตราภาษีของเซลล์ D2 แล้ว เพื่อให้ได้ผลลัพธ์ที่เหลือคุณจะต้อง แปลงการอ้างอิงเซลล์ของ lookup_array และ return_array เป็นค่าสัมบูรณ์.
- ดับเบิลคลิกเซลล์ E2 เพื่อแสดงสูตร =XLOOKUP(D2,B2:B8,A2:A8,,1);
- เลือกช่วงการค้นหา B2:B8 ในสูตร กดปุ่ม F4 เพื่อรับ $B$2:$B$8;
- เลือกช่วงผลตอบแทน A2:A8 ในสูตร กดปุ่ม F4 เพื่อรับ $A$2:$A$8;
- กด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์ของเซลล์ E2
![]() |
>>> | ![]() |
3 แล้วก็ ลากที่จับเติมลง เพื่อให้ได้ผลลัพธ์ทั้งหมด
√ หมายเหตุ:
- กดปุ่ม F4 บนแป้นพิมพ์ช่วยให้คุณ เปลี่ยนการอ้างอิงเซลล์เป็นการอ้างอิงแบบสัมบูรณ์ โดยการเพิ่มเครื่องหมายดอลลาร์ก่อนแถวและคอลัมน์
- หลังจากใช้การอ้างอิงแบบสัมบูรณ์ เพื่อค้นหาและส่งคืนช่วง เราเปลี่ยน สูตรในเซลล์ E2 สำหรับรุ่นนี้:
=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)
- เมื่อคุณ ลากที่จับเติมลงจากเซลล์ E2, สูตร ในแต่ละเซลล์ของคอลัมน์ E คือ เปลี่ยนเฉพาะในด้านของ lookup_value.
ตัวอย่างเช่น ตอนนี้สูตรใน E13 เปลี่ยนเป็นสิ่งนี้:
=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)
ตัวอย่างที่ 3: การจับคู่สัญลักษณ์แทน
ทำการแข่งขัน Wildcard ด้วย XLOOKUP
ก่อนที่เราจะมองเข้าไปใน ฟังก์ชันการจับคู่สัญลักษณ์แทน XLOOKUPมาดูกันก่อน ไวลด์การ์ดคืออะไร.
ในไมโครซอฟต์เอ็กเซล ไวด์การ์ดเป็นอักขระชนิดพิเศษที่สามารถแทนที่อักขระใดก็ได้. โดยเฉพาะอย่างยิ่ง เป็นประโยชน์ เมื่อคุณต้องการดำเนินการค้นหาการจับคู่บางส่วน
ไวลด์การ์ดมีสามประเภท: เครื่องหมายดอกจัน (*), เครื่องหมายคำถาม (?)และ ตัวหนอน (~).
- เครื่องหมายดอกจัน (*) หมายถึงจำนวนอักขระในข้อความ
- เครื่องหมายคำถาม (?) ย่อมาจากอักขระตัวเดียวในข้อความ
- ตัวหนอน (~) ใช้เพื่อเปลี่ยนอักขระตัวแทน (*, ? ~) เป็นอักขระตามตัวอักษร วางตัวหนอน (~) ไว้หน้าสัญลักษณ์แทนเพื่อทำหน้าที่นี้ให้สมบูรณ์
ในกรณีส่วนใหญ่ เมื่อเราดำเนินการฟังก์ชันการจับคู่สัญลักษณ์แทน XLOOKUP เราจะใช้อักขระดอกจัน (*) ตอนนี้เรามาดูกันว่าการจับคู่ไวด์การ์ดทำงานอย่างไร
สมมติว่าคุณมีรายชื่อของมูลค่าหลักทรัพย์ตามราคาตลาดของบริษัทอเมริกันที่ใหญ่ที่สุด 50 แห่ง และต้องการทราบมูลค่าตามราคาตลาดของบริษัทสองสามแห่ง แต่ชื่อบริษัทนั้นสั้น นี่คือสถานการณ์สมมติที่สมบูรณ์แบบสำหรับการจับคู่สัญลักษณ์แทน โปรดปฏิบัติตามฉันทีละขั้นตอนเพื่อทำเคล็ดลับ
√ หมายเหตุ: หากต้องการจับคู่สัญลักษณ์แทน สิ่งสำคัญที่สุดคือตั้งค่าอาร์กิวเมนต์ที่ห้า [match_mode] เป็น 2
1. พิมพ์ ด้านล่างสูตร ไปที่เซลล์ว่าง H3 แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)
![]() |
>>> | ![]() |
2. ตอนนี้คุณรู้ผลลัพธ์ของเซลล์ H3 แล้ว เพื่อให้ได้ผลลัพธ์ที่เหลือ คุณต้อง ทำให้ lookup_array และ return_array คงที่ โดยวางเคอร์เซอร์ในอาร์เรย์แล้วกดปุ่ม F4 จากนั้นสูตรใน H3 จะกลายเป็น:
=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)
3. ลากที่จับเติมลง เพื่อให้ได้ผลลัพธ์ทั้งหมด
√ หมายเหตุ:
- lookup_value ของสูตรในเซลล์ H3 คือ "*"&G3&"*" เรา เชื่อมเครื่องหมายดอกจัน (*) กับค่า G3 โดยใช้โปรแกรม เครื่องหมาย (&).
- อาร์กิวเมนต์ที่สี่ [If_not_found] เป็นทางเลือก ฉันก็เลยข้ามไป
ตัวอย่างที่ 4: มองไปทางซ้าย
หนึ่ง ข้อเสียของ VLOOKUP มันคืออะไร ถูกจำกัดให้ดำเนินการค้นหาทางด้านขวาของคอลัมน์ค้นหา. หากคุณพยายามค้นหาค่าที่เหลือในคอลัมน์การค้นหา คุณจะได้รับข้อผิดพลาด #N/A ไม่ต้องกังวล XLOOKUP เป็นฟังก์ชันการค้นหาที่สมบูรณ์แบบในการแก้ปัญหานี้
เอ็กซ์ลุคอัพ ออกแบบมาเพื่อค้นหาค่าของ ซ้ายหรือขวา ของคอลัมน์ค้นหา ไม่มีข้อจำกัดและตรงตามความต้องการของผู้ใช้ Excel ในตัวอย่างด้านล่าง เราจะแสดงเคล็ดลับให้คุณดู
สมมติว่าคุณมีรายชื่อประเทศที่มีรหัสโทรศัพท์ และคุณต้องการค้นหาชื่อประเทศด้วยรหัสโทรศัพท์ที่ทราบ
เราจำเป็นต้องค้นหาคอลัมน์ C และคืนค่าในคอลัมน์ A โปรดทำดังนี้:
1. พิมพ์ ด้านล่างสูตร ลงในเซลล์ว่าง G2
=XLOOKUP(F2,C2:C11,A2:A11)
2 กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
√ หมายเหตุ: ฟังก์ชัน XLOOKUP มองไปทางซ้ายสามารถแทนที่ Index และ Match เพื่อค้นหาค่าทางด้านซ้าย
ค้นหาค่าจากขวาไปซ้ายด้วยการคลิกเพียงไม่กี่ครั้ง
สำหรับใครที่ไม่อยากจำสูตร ที่นี่ขอแนะนำ คุณสมบัติที่มีประโยชน์ - ค้นหาจากขวาไปซ้าย of Kutools for Excel. ด้วยคุณสมบัตินี้ คุณสามารถค้นหาจากขวาไปซ้ายได้ภายในไม่กี่วินาที
ด้วย our โปรแกรมเสริม Excel สถาบันทั้งหมดโปรดทำดังนี้:
1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก ค้นหาจากขวาไปซ้าย ในรายการแบบหล่นลง
2 ใน ค้นหาจากกล่องโต้ตอบขวาไปซ้ายคุณต้องกำหนดค่าดังนี้:
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ค่าการค้นหาและส่วนช่วงเอาต์พุต, ระบุ ช่วงการค้นหา และ ช่วงเอาท์พุท;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูล, อินพุต ช่วงข้อมูลแล้วระบุ คอลัมน์สำคัญ และ กลับคอลัมน์;
3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์
ตัวอย่างที่ 5: ค้นหาแนวตั้งหรือแนวนอน
ดำเนินการค้นหาแนวตั้งหรือแนวนอนด้วย XLOOKUP
ในฐานะผู้ใช้ Excel คุณอาจคุ้นเคยกับฟังก์ชัน VLOOKUP และ HLOOKUP VLOOKUP คือการมองในแนวตั้งในคอลัมน์ และ HLOOKUP คือการมองในแนวนอนเป็นแถว.
XLOOKUP ใหม่รวมทั้งสองอย่างเข้าด้วยกัน, หมายความว่า คุณจำเป็นต้องใช้ไวยากรณ์เดียวเพื่อทำการค้นหาแนวตั้งหรือการค้นหาแนวนอน. อัจฉริยะใช่มั้ย?
ในตัวอย่างด้านล่าง เราจะอธิบายวิธีที่คุณใช้ไวยากรณ์ XLOOKUP เพียงรูปแบบเดียวในการเรียกใช้การค้นหาในแนวตั้งหรือแนวนอน
เพื่อทำการค้นหาแนวตั้ง, พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง E2 ให้กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP(E1,A2:A13,B2:B13)
เพื่อทำการค้นหาแนวนอน, พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง P2 ให้กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP(P1,B1:M1,B2:M2)
ที่คุณสามารถดู, ไวยากรณ์เหมือนกัน. ความแตกต่างเท่านั้น ระหว่างสองสูตรคือคุณป้อน คอลัมน์ ในการค้นหาแนวตั้งในขณะที่คุณป้อน แถว ในการค้นหาแนวนอน
ตัวอย่างที่ 6: การค้นหาแบบสองทาง
ทำการค้นหาแบบสองทางด้วย XLOOKUP
คุณยังใช้อยู่หรือเปล่า ฟังก์ชัน INDEX และ MATCH เพื่อค้นหาค่าในช่วงสองมิติ? ลอง XLOOKUP . ที่ปรับปรุงแล้ว เพื่อให้งานของคุณเสร็จสิ้น ง่ายขึ้น.
XLOOKUP สามารถทำได้ ค้นหาสองครั้ง, หา การตัด ของสองค่า โดย การทำรัง XLOOKUP หนึ่งอันในอีกอันหนึ่ง XLOOKUP ด้านในสามารถส่งคืนทั้งแถวหรือทั้งคอลัมน์ จากนั้นแถวหรือคอลัมน์ที่ส่งคืนนี้จะถูกป้อนลงใน XLOOKUP ภายนอกเป็นอาร์เรย์ส่งคืน
สมมติว่าคุณมีรายชื่อเกรดของนักเรียนที่มีสาขาวิชาต่างกัน คุณต้องการทราบเกรดของวิชาเคมีของคิม
เรามาดูกันว่าเราใช้ XLOOKUP มหัศจรรย์ในการทำกลลวงได้อย่างไร
- เราเรียกใช้ XLOOKUP "ภายใน" เพื่อส่งกลับค่าของคอลัมน์ enter XLOOKUP(H2,B1:E1,B2:E10) สามารถรับเกรดเคมีได้หลากหลาย
- เราซ้อน XLOOKUP "ภายใน" ภายใน XLOOKUP "ภายนอก" โดยใช้ XLOOKUP "ภายใน" เป็น return_array ในสูตรที่สมบูรณ์
- แล้วสูตรสุดท้ายก็มาถึง:
=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))
- พิมพ์ สูตรข้างบน ลงในเซลล์ว่าง H3 ให้กด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์
หรือจะทำก็ได้ วิธีอื่น ๆให้ใช้ "inner" XLOOKUP เพื่อคืนค่าของทั้งแถว ซึ่งเป็นเกรดหัวเรื่องทั้งหมดของ Kim จากนั้นใช้ "ชั้นนอก" XLOOKUP เพื่อค้นหาเกรดเคมีจากคะแนนวิชาทั้งหมดของ Kim
- พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง H4 แล้วกด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์
=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))
ฟังก์ชันการค้นหาแบบสองทางของ XLOOKUP ยังเป็นภาพประกอบที่สมบูรณ์แบบของฟังก์ชันการค้นหาในแนวตั้งและแนวนอนอีกด้วย ได้ลองถ้าคุณต้องการ!
ตัวอย่างที่ 7: ปรับแต่งข้อความไม่พบ
ปรับแต่งข้อความไม่พบโดยใช้ XLOOKUP
เช่นเดียวกับฟังก์ชันการค้นหาอื่นๆ เมื่อฟังก์ชัน XLOOKUP หาที่ตรงกันไม่ได้ที่ #N/A ข้อความแสดงข้อผิดพลาด จะถูกส่งกลับ อาจสร้างความสับสนให้กับผู้ใช้ Excel บางราย แต่ข่าวดีก็คือ การจัดการข้อผิดพลาด มีอยู่ในไฟล์ อาร์กิวเมนต์ที่สี่ของฟังก์ชัน XLOOKUP.
กับ อาร์กิวเมนต์ [if_not_found] ในตัว, คุณสามารถระบุ ข้อความที่กำหนดเองเพื่อแทนที่ผลลัพธ์ #N/A. พิมพ์ข้อความที่คุณต้องการในอาร์กิวเมนต์ที่สี่และใส่ข้อความใน เครื่องหมายคำพูดคู่ (").
ตัวอย่างเช่น ไม่พบเมือง Denver ดังนั้น XLOOKUP จะส่งกลับข้อความแสดงข้อผิดพลาด #N/A แต่หลังจากที่เราปรับแต่งอาร์กิวเมนต์ที่สี่ด้วยข้อความ "No Match" สูตรจะแสดงข้อความ "No Match" แทนข้อความแสดงข้อผิดพลาด
พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง F3 แล้วกด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์
=XLOOKUP(E2,A2:A11,C2:C11,"No Match")
ปรับแต่งข้อผิดพลาด #N/A ด้วยคุณสมบัติที่มีประโยชน์
หากต้องการแทนที่ข้อผิดพลาด #N/A ด้วยข้อความที่คุณกำหนดเองอย่างรวดเร็ว Kutools for Excel is เครื่องมือที่สมบูรณ์แบบ ใน Excel เพื่อช่วยคุณ ด้วยบิวด์อิน แทนที่ 0 หรือ #N/A ด้วยคุณสมบัติค่าว่างหรือค่าเฉพาะคุณสามารถระบุข้อความที่ไม่พบได้โดยไม่ต้องใช้สูตรที่ซับซ้อนหรือเข้าถึง XLOOKUP
กับของเรา โปรแกรมเสริม Excel ติดตั้งแล้ว โปรดทำดังนี้:
1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก แทนที่ 0 หรือ # N / A ด้วยค่าว่างหรือค่าเฉพาะ ในรายการแบบหล่นลง
2 ใน แทนที่ 0 หรือ #N/A ด้วยกล่องโต้ตอบค่าว่างหรือค่าเฉพาะคุณต้องกำหนดค่าดังนี้:
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ค่าการค้นหาและส่วนช่วงเอาต์พุตให้เลือก ช่วงการค้นหา และ ช่วงเอาท์พุท;
- แล้วก็ เลือกตัวเลือกแทนที่ 0 หรือ #N/A ด้วยค่าเฉพาะตัวเลือก, ใส่ข้อความ คุณชอบ;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูลให้เลือก ช่วงข้อมูลจากนั้นระบุไฟล์ คอลัมน์สำคัญ และ กลับคอลัมน์.
3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์ ข้อความที่กำหนดเองจะปรากฏขึ้นเมื่อไม่พบรายการที่ตรงกัน
ตัวอย่างที่ 8: ค่าหลายค่า
อื่น ความได้เปรียบ ของ XLOOKUP คือความสามารถในการ ส่งคืนค่าหลายค่า ในเวลาเดียวกันสำหรับการแข่งขันเดียวกัน ป้อนสูตรหนึ่งเพื่อให้ได้ผลลัพธ์แรก จากนั้นจึงคืนค่าอื่นๆ กระฉอก ลงในเซลล์ว่างข้างเคียงโดยอัตโนมัติ
ในตัวอย่างด้านล่าง คุณต้องการรับข้อมูลทั้งหมดเกี่ยวกับรหัสนักศึกษา “FG9940005” เคล็ดลับคือการระบุช่วงเป็น return_array ในสูตร แทนที่จะเป็นคอลัมน์หรือแถวเดียว ในกรณีนี้ ช่วงอาร์เรย์ส่งคืนคือ B2:D9 รวมถึงสามคอลัมน์
พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง G2 ให้กด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ทั้งหมด
=XLOOKUP(F2,A2:A9,B2:D9)
เซลล์ผลลัพธ์ทั้งหมดแสดงสูตรเดียวกัน คุณสามารถ แก้ไขหรือแก้ไข สูตร ในเซลล์แรกแต่ ในเซลล์อื่นๆ สูตรไม่สามารถแก้ไขได้. คุณสามารถเห็นแถบสูตรคือ เป็นสีเทาหมายความว่าไม่สามารถเปลี่ยนแปลงได้
สรุปแล้ว ฟังก์ชันหลายค่าของ XLOOKUP คือ a การปรับปรุงที่เป็นประโยชน์ เทียบกับ VLOOKUP คุณไม่ต้องระบุหมายเลขคอลัมน์ทุกคอลัมน์แยกกันสำหรับแต่ละสูตร ยกนิ้วให้!
ตัวอย่างที่ 9 เกณฑ์หลายเกณฑ์
ทำการค้นหาแบบหลายเกณฑ์โดยใช้ XLOOKUP
อื่น คุณสมบัติใหม่ที่น่าตื่นตาตื่นใจ ของ XLOOKUP คือความสามารถในการ ค้นหาด้วยหลายเกณฑ์. เคล็ดลับคือการ concatenate ค่าการค้นหาและอาร์เรย์การค้นหาด้วย "&" โอเปอเรเตอร์ แยกกันในสูตร มาอธิบายผ่านตัวอย่างด้านล่าง
เราต้องรู้ราคาแจกันสีน้ำเงินกลาง ในกรณีนี้ ต้องใช้ค่าการค้นหาสามค่า (เกณฑ์) เพื่อค้นหาค่าที่ตรงกัน พิมพ์ สูตรด้านล่าง ในเซลล์ว่าง I2 จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ หมายเหตุ: XLOOKUP สามารถประมวลผลอาร์เรย์ได้โดยตรง ไม่จำเป็นต้องยืนยันสูตรด้วย Control + Shift + Enter
ค้นหาหลายเงื่อนไขด้วยวิธีที่รวดเร็ว
มีผู้ใด เร็วขึ้นและง่ายขึ้น วิธีดำเนินการค้นหาแบบหลายเกณฑ์มากกว่า XLOOKUP ใน excel Kutools for Excel ให้ คุณสมบัติที่น่าทึ่ง - การค้นหาแบบหลายเงื่อนไข. ด้วยคุณสมบัตินี้ คุณสามารถเรียกใช้การค้นหาเกณฑ์ได้หลายแบบด้วยการคลิกเพียงไม่กี่ครั้ง!
กับของเรา โปรแกรมเสริม Excel ติดตั้งแล้ว โปรดทำดังนี้:
1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก การค้นหาแบบหลายเงื่อนไข ในรายการแบบหล่นลง
2 ใน กล่องโต้ตอบการค้นหาแบบหลายเงื่อนไขโปรดดำเนินการดังนี้:
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนการค้นหาค่าและช่วงเอาต์พุตให้เลือก ค้นหาช่วงค่า และ ช่วงเอาท์พุท;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูลโปรดดำเนินการดังต่อไปนี้:
- เลือก คอลัมน์หลักที่เกี่ยวข้อง ที่มีค่าการค้นหาทีละตัวโดยกด . ค้างไว้ Ctrl คีย์ในไฟล์ กล่องใส่กุญแจ;
- ระบุ คอลัมน์ ซึ่งประกอบด้วยค่าที่ส่งคืนใน กลับกล่องคอลัมน์.
3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์
√ หมายเหตุ:
- ส่วนค่าความผิดพลาด แทนที่ #N/A ด้วยค่าที่ระบุ เป็นทางเลือกในกล่องโต้ตอบ คุณสามารถระบุหรือไม่ระบุก็ได้
- จำนวนคอลัมน์ที่ป้อนในกล่องคอลัมน์คีย์ต้องเท่ากับจำนวนคอลัมน์ที่ป้อนในกล่องค้นหาค่า และลำดับของเกณฑ์ในทั้งสองกล่องต้องสอดคล้องกัน
ตัวอย่างที่ 10. ค้นหาค่าด้วยการจับคู่ครั้งสุดท้าย
รับผลการจับคู่ล่าสุดกับ XLOOKUP
เพื่อค้นหา ค่าที่ตรงกันล่าสุด ใน Excel ให้ตั้งค่า อาร์กิวเมนต์ที่หก ในฟังก์ชัน XLOOKUP ถึง ค้นหาในลำดับที่กลับกัน.
โดยค่าเริ่มต้น โหมดค้นหาใน XLOOKUP ถูกตั้งค่าเป็น 1ซึ่งเป็น ค้นหาจากแรกไปสุดท้าย. แต่ข้อดีของ เอ็กซ์ลุคอัพ คือ ทิศทางการค้นหาสามารถเปลี่ยนแปลงได้. XLOOKUP เสนอ ตัวเลือก [โหมดค้นหา] อาร์กิวเมนต์ เพื่อควบคุมลำดับการค้นหา เพียงตั้งค่าโหมดการค้นหาในอาร์กิวเมนต์ที่หกเป็น -1ทิศทางการค้นหาจะเปลี่ยนเป็นการค้นหาจากล่าสุดไปเป็นอันดับแรก
โปรดดูตัวอย่างด้านล่าง เราต้องการทราบยอดขายล่าสุดของ Emma ในฐานข้อมูล
พิมพ์ สูตรด้านล่าง ในเซลล์ว่าง G2 จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ หมายเหตุ: อาร์กิวเมนต์ที่สี่และห้าเป็นทางเลือกและละเว้นในกรณีนี้ เราตั้งค่าอาร์กิวเมนต์ตัวเลือกที่หกเป็น -1 เท่านั้น
ค้นหาค่าที่ตรงกันล่าสุดได้อย่างง่ายดายด้วยเครื่องมือที่น่าทึ่ง
ในกรณีที่คุณไม่สามารถเข้าถึง XLOOKUP และไม่ต้องการจำสูตรที่ซับซ้อน คุณสามารถใช้ ค้นหาจากล่างขึ้นบน คุณสมบัติ of Kutools for Excel เพื่อให้เสร็จ อย่างง่ายดาย.
กับของเรา โปรแกรมเสริม Excel ติดตั้งแล้ว โปรดทำดังนี้:
1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก ค้นหาจากล่างขึ้นบน ในรายการแบบหล่นลง
2 ใน ค้นหาจากล่างขึ้นบนกล่องโต้ตอบคุณต้องกำหนดค่าดังนี้:
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ค่าการค้นหาและส่วนช่วงเอาต์พุตให้เลือก ระยะการค้นหา และ ช่วงเอาท์พุท;
- ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูลให้เลือก ช่วงข้อมูลจากนั้นระบุไฟล์ คอลัมน์สำคัญ และ กลับคอลัมน์.
3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์
√ หมายเหตุ: ค่าความผิดพลาด แทนที่ #N/A ด้วยค่าที่ระบุ เป็นทางเลือกในกล่องโต้ตอบ คุณสามารถระบุหรือไม่ก็ได้
ดาวน์โหลด XLOOKUP ไฟล์ตัวอย่าง
บทความที่เกี่ยวข้อง:
- วิธีการใช้ INDEX และ MATCH ร่วมกันใน Excel?
- วิธีการใช้ Fuzzy Lookup เพื่อค้นหาผลการแข่งขันโดยประมาณใน Excel?
- วิธีการใช้สูตรการค้นหาแบบสองทางใน Excel?
- วิธีการ Vlookup มูลค่าด้วยหลายเกณฑ์ใน Excel?
- วิธีการ Vlookup ค่าจากขวาไปซ้ายใน Excel?
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools for Excel แก้ปัญหาส่วนใหญ่ของคุณ และเพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 80%
- ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
- ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
- เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
- สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
- แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
- แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
- ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
- รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
- การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...

- เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
- เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
- เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
