วิธีใช้ฟังก์ชัน XLOOKUP ใหม่และขั้นสูงใน Excel (10 ตัวอย่าง)

เอ็กเซล ใหม่ล่าสุด XLOOKUP เป็นฟังก์ชันการค้นหาที่มีประสิทธิภาพและง่ายที่สุดที่ Excel สามารถนำเสนอได้ ด้วยความพยายามอย่างไม่ลดละ ในที่สุด Microsoft ก็ปล่อยฟังก์ชัน XLOOKUP นี้เพื่อแทนที่ VLOOKUP, HLOOKUP, INDEX+MATCH และฟังก์ชันการค้นหาอื่นๆ

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

จะรับ XLOOKUP ได้อย่างไร?

XLOOKUP ไวยากรณ์ของฟังก์ชัน

ตัวอย่างฟังก์ชัน XLOOKUP

• ตัวอย่างที่ 1: การจับคู่แบบตรงทั้งหมด
  • ทำการจับคู่แบบตรงทั้งหมดด้วย XLOOKUP
  • เพียงไม่กี่คลิกเพื่อให้ได้ผลลัพธ์ที่ตรงกันทุกประการ

• ตัวอย่างที่ 2: การจับคู่โดยประมาณ
  • ทำการแข่งขันโดยประมาณด้วย XLOOKUP

• ตัวอย่างที่ 3: การจับคู่สัญลักษณ์แทน
  • ทำการแข่งขัน Wildcard ด้วย XLOOKUP

• ตัวอย่างที่ 4: มองไปทางซ้าย
  • มองไปทางซ้ายโดยใช้ XLOOKUP
  • ค้นหาค่าจากขวาไปซ้ายด้วยการคลิกเพียงไม่กี่ครั้ง

• ตัวอย่างที่ 5: ค้นหาแนวตั้งหรือแนวนอน
  • ดำเนินการค้นหาแนวตั้งหรือแนวนอนด้วย XLOOKUP

• ตัวอย่างที่ 6: การค้นหาแบบสองทาง
  • ทำการค้นหาแบบสองทางด้วย XLOOKUP

• ตัวอย่างที่ 7: ปรับแต่งข้อความไม่พบ
  • ปรับแต่งข้อความไม่พบโดยใช้ XLOOKUP
  • ปรับแต่งข้อผิดพลาด #N/A ด้วยคุณสมบัติที่มีประโยชน์

• ตัวอย่างที่ 8: ค่าหลายค่า
  • ส่งคืนค่าหลายค่าด้วย XLOOKUP

• ตัวอย่างที่ 9: เกณฑ์หลายเกณฑ์
  • ทำการค้นหาแบบหลายเกณฑ์โดยใช้ XLOOKUP
  • ค้นหาหลายเงื่อนไขด้วยวิธีที่รวดเร็ว

• ตัวอย่างที่ 10: ค้นหาค่าที่ตรงกันล่าสุด
  • รับผลการจับคู่ล่าสุดกับ XLOOKUP
  • ค้นหาค่าที่ตรงกันล่าสุดได้อย่างง่ายดายด้วยเครื่องมือที่น่าทึ่ง

ดาวน์โหลด XLOOKUP ไฟล์ตัวอย่าง

จะรับ XLOOKUP ได้อย่างไร?

ตั้งแต่ ฟังก์ชัน XLOOKUP is ใช้ได้เฉพาะ in Excel สำหรับ Microsoft 365, 2021 Excelและ Excel สำหรับเว็บคุณสามารถอัปเกรด Excel เป็นเวอร์ชันที่พร้อมใช้งานเพื่อรับ XLOOKUP

XLOOKUP ไวยากรณ์ของฟังก์ชัน

ฟังก์ชัน XLOOKUP ค้นหาช่วงหรืออาร์เรย์แล้วคืนค่าของผลลัพธ์ที่ตรงกันครั้งแรก. วากยสัมพันธ์ ของฟังก์ชัน XLOOKUP มีดังนี้:

ข้อโต้แย้ง:

1. Lookup_value (จำเป็น): ความคุ้มค่าที่คุณต้องการ สามารถอยู่ในคอลัมน์ใดๆ ของช่วง table_array
2. lookup_array (จำเป็น): อาร์เรย์หรือช่วงที่คุณค้นหาค่าการค้นหา
3. Return_array (จำเป็น): อาร์เรย์หรือช่วงจากตำแหน่งที่คุณต้องการรับค่า
4. If_not_found (ไม่บังคับ): ค่าที่จะส่งคืนเมื่อไม่พบการจับคู่ที่ถูกต้อง คุณสามารถปรับแต่งข้อความใน [if_not_found] เพื่อแสดงว่าไม่มีรายการที่ตรงกัน
   มิฉะนั้น ค่าที่ส่งคืนจะเป็น #N/A โดยค่าเริ่มต้น
5. Match_mode (ไม่บังคับ): ที่นี่ คุณสามารถระบุวิธีจับคู่ lookup_value กับค่าใน lookup_array
   • 0 (ค่าเริ่มต้น) = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้ส่งคืน #N/A
   • -1 = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้คืนค่าที่น้อยกว่าถัดไป
   • 1 = ตรงทั้งหมด หากไม่พบรายการที่ตรงกัน ให้คืนค่าที่มากกว่าถัดไป
   • 2 = การแข่งขันบางส่วน ใช้อักขระตัวแทนเช่น *, ? และ ~ เพื่อเรียกใช้การจับคู่ไวด์การ์ด
6. 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 สำหรับ 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 ปุ่มเพื่อรับผลลัพธ์

คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้งานฟรี 30 วัน.

---

ตัวอย่างที่ 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: มองไปทางซ้าย

มองไปทางซ้ายโดยใช้ XLOOKUP

หนึ่ง ข้อเสียของ VLOOKUP มันคืออะไร ถูกจำกัดให้ดำเนินการค้นหาทางด้านขวาของคอลัมน์ค้นหา. หากคุณพยายามค้นหาค่าที่เหลือในคอลัมน์การค้นหา คุณจะได้รับข้อผิดพลาด #N/A ไม่ต้องกังวล XLOOKUP เป็นฟังก์ชันการค้นหาที่สมบูรณ์แบบในการแก้ปัญหานี้

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

สมมติว่าคุณมีรายชื่อประเทศที่มีรหัสโทรศัพท์ และคุณต้องการค้นหาชื่อประเทศด้วยรหัสโทรศัพท์ที่ทราบ

เราจำเป็นต้องค้นหาคอลัมน์ C และคืนค่าในคอลัมน์ A โปรดทำดังนี้:

1. พิมพ์ ด้านล่างสูตร ลงในเซลล์ว่าง G2

=XLOOKUP(F2,C2:C11,A2:A11)

2 กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

√ หมายเหตุ: ฟังก์ชัน XLOOKUP มองไปทางซ้ายสามารถแทนที่ Index และ Match เพื่อค้นหาค่าทางด้านซ้าย

ค้นหาค่าจากขวาไปซ้ายด้วยการคลิกเพียงไม่กี่ครั้ง

สำหรับใครที่ไม่อยากจำสูตร ที่นี่ขอแนะนำ คุณสมบัติที่มีประโยชน์ - ค้นหาจากขวาไปซ้าย of Kutools สำหรับ Excel. ด้วยคุณสมบัตินี้ คุณสามารถค้นหาจากขวาไปซ้ายได้ภายในไม่กี่วินาที

ด้วย our โปรแกรมเสริม Excel สถาบันทั้งหมดโปรดทำดังนี้:

1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก ค้นหาจากขวาไปซ้าย ในรายการแบบหล่นลง

2 ใน ค้นหาจากกล่องโต้ตอบขวาไปซ้ายคุณต้องกำหนดค่าดังนี้:

• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ค่าการค้นหาและส่วนช่วงเอาต์พุต, ระบุ ช่วงการค้นหา และ ช่วงเอาท์พุท;
• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูล, อินพุต ช่วงข้อมูลแล้วระบุ คอลัมน์สำคัญ และ กลับคอลัมน์;

3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์

คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้งานฟรี 30 วัน.

---

ตัวอย่างที่ 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 สำหรับ 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 ปุ่มเพื่อรับผลลัพธ์ ข้อความที่กำหนดเองจะปรากฏขึ้นเมื่อไม่พบรายการที่ตรงกัน

คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้งานฟรี 30 วัน.

---

ตัวอย่างที่ 8: ค่าหลายค่า

ส่งคืนค่าหลายค่าด้วย XLOOKUP

อื่น ความได้เปรียบ ของ 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 สำหรับ Excel ให้ คุณสมบัติที่น่าทึ่ง - การค้นหาแบบหลายเงื่อนไข. ด้วยคุณสมบัตินี้ คุณสามารถเรียกใช้การค้นหาเกณฑ์ได้หลายแบบด้วยการคลิกเพียงไม่กี่ครั้ง!

กับของเรา โปรแกรมเสริม Excel ติดตั้งแล้ว โปรดทำดังนี้:

1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก การค้นหาแบบหลายเงื่อนไข ในรายการแบบหล่นลง

2 ใน กล่องโต้ตอบการค้นหาแบบหลายเงื่อนไขโปรดดำเนินการดังนี้:

• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนการค้นหาค่าและช่วงเอาต์พุตให้เลือก ค้นหาช่วงค่า และ ช่วงเอาท์พุท;
• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูลโปรดดำเนินการดังต่อไปนี้:
  • เลือก คอลัมน์หลักที่เกี่ยวข้อง ที่มีค่าการค้นหาทีละตัวโดยกด . ค้างไว้ Ctrl คีย์ในไฟล์ กล่องใส่กุญแจ;
  • ระบุ คอลัมน์ ซึ่งประกอบด้วยค่าที่ส่งคืนใน กลับกล่องคอลัมน์.

3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์

√ หมายเหตุ:

• ส่วนค่าความผิดพลาด แทนที่ #N/A ด้วยค่าที่ระบุ เป็นทางเลือกในกล่องโต้ตอบ คุณสามารถระบุหรือไม่ระบุก็ได้
• จำนวนคอลัมน์ที่ป้อนในกล่องคอลัมน์คีย์ต้องเท่ากับจำนวนคอลัมน์ที่ป้อนในกล่องค้นหาค่า และลำดับของเกณฑ์ในทั้งสองกล่องต้องสอดคล้องกัน

คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้งานฟรี 30 วัน.

---

ตัวอย่างที่ 10. ค้นหาค่าด้วยการจับคู่ครั้งสุดท้าย

รับผลการจับคู่ล่าสุดกับ XLOOKUP

เพื่อค้นหา ค่าที่ตรงกันล่าสุด ใน Excel ให้ตั้งค่า อาร์กิวเมนต์ที่หก ในฟังก์ชัน XLOOKUP ถึง ค้นหาในลำดับที่กลับกัน.

โดยค่าเริ่มต้น โหมดค้นหาใน XLOOKUP ถูกตั้งค่าเป็น 1ซึ่งเป็น ค้นหาจากแรกไปสุดท้าย. แต่ข้อดีของ XLOOKUP คือ ทิศทางการค้นหาสามารถเปลี่ยนแปลงได้. XLOOKUP เสนอ ตัวเลือก [โหมดค้นหา] อาร์กิวเมนต์ เพื่อควบคุมลำดับการค้นหา เพียงตั้งค่าโหมดการค้นหาในอาร์กิวเมนต์ที่หกเป็น -1ทิศทางการค้นหาจะเปลี่ยนเป็นการค้นหาจากล่าสุดไปเป็นอันดับแรก

โปรดดูตัวอย่างด้านล่าง เราต้องการทราบยอดขายล่าสุดของ Emma ในฐานข้อมูล

พิมพ์ สูตรด้านล่าง ในเซลล์ว่าง G2 จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ หมายเหตุ: อาร์กิวเมนต์ที่สี่และห้าเป็นทางเลือกและละเว้นในกรณีนี้ เราตั้งค่าอาร์กิวเมนต์ตัวเลือกที่หกเป็น -1 เท่านั้น

ค้นหาค่าที่ตรงกันล่าสุดได้อย่างง่ายดายด้วยเครื่องมือที่น่าทึ่ง

ในกรณีที่คุณไม่สามารถเข้าถึง XLOOKUP และไม่ต้องการจำสูตรที่ซับซ้อน คุณสามารถใช้ ค้นหาจากล่างขึ้นบน คุณสมบัติ of Kutools สำหรับ Excel เพื่อให้เสร็จ อย่างง่ายดาย.

กับของเรา โปรแกรมเสริม Excel ติดตั้งแล้ว โปรดทำดังนี้:

1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก ค้นหาจากล่างขึ้นบน ในรายการแบบหล่นลง

2 ใน ค้นหาจากล่างขึ้นบนกล่องโต้ตอบคุณต้องกำหนดค่าดังนี้:

• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ค่าการค้นหาและส่วนช่วงเอาต์พุตให้เลือก ระยะการค้นหา และ ช่วงเอาท์พุท;
• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนช่วงข้อมูลให้เลือก ช่วงข้อมูลจากนั้นระบุไฟล์ คอลัมน์สำคัญ และ กลับคอลัมน์.

3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์

√ หมายเหตุ: ค่าความผิดพลาด แทนที่ #N/A ด้วยค่าที่ระบุ เป็นทางเลือกในกล่องโต้ตอบ คุณสามารถระบุหรือไม่ก็ได้

คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้งานฟรี 30 วัน.

---

ดาวน์โหลด XLOOKUP ไฟล์ตัวอย่าง

ตัวอย่าง XLOOKUP.xlsx

บทความที่เกี่ยวข้อง:

• วิธีการใช้ INDEX และ MATCH ร่วมกันใน Excel?
• วิธีการใช้ Fuzzy Lookup เพื่อค้นหาผลการแข่งขันโดยประมาณใน Excel?
• วิธีการใช้สูตรการค้นหาแบบสองทางใน Excel?
• วิธีการ Vlookup มูลค่าด้วยหลายเกณฑ์ใน Excel?
• วิธีการ Vlookup ค่าจากขวาไปซ้ายใน Excel?