Note: The other languages of the website are Google-translated. Back to English

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

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

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

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

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

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

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

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

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

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

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

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

ฟังก์ชัน xlookup 1

ข้อโต้แย้ง:

  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. พิมพ์ ด้านล่างไวยากรณ์ ลงในเซลล์ว่าง โปรดทราบว่าคุณต้องพิมพ์วงเล็บด้านเดียวเท่านั้น

=XLOOKUP(

ฟังก์ชัน xlookup 2

2 กด Ctrl + Aแล้ว กล่องข้อความ ปรากฏขึ้นซึ่งแสดงให้เห็น อาร์กิวเมนต์ฟังก์ชัน. และอีกด้านของโครงยึดจะเสร็จสิ้นโดยอัตโนมัติ

ฟังก์ชัน xlookup 3

3. ดึงแผงข้อมูลลงแล้วคุณจะมองเห็นทั้งหมด อาร์กิวเมนต์หกฟังก์ชัน ของ XLOOKUP

ฟังก์ชัน xlookup 4 >>> ฟังก์ชัน xlookup 5

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

ฉันแน่ใจว่าคุณเข้าใจหลักการพื้นฐานของฟังก์ชัน XLOOKUP แล้ว มาดำดิ่งลงไปใน ตัวอย่างการปฏิบัติ ของ XLOOKUP

ตัวอย่างที่ 1: การจับคู่แบบตรงทั้งหมด

ทำการจับคู่แบบตรงทั้งหมดด้วย XLOOKUP

คุณเคยหงุดหงิดเพราะต้องระบุโหมดการจับคู่แบบตรงทั้งหมดทุกครั้งที่ใช้ VLOOKUP หรือไม่? โชคดีที่ปัญหานี้ไม่มีอีกต่อไปเมื่อคุณลองใช้ฟังก์ชัน XLOOKUP อันน่าทึ่ง โดยค่าเริ่มต้น XLOOKUP จะสร้างการจับคู่แบบตรงทั้งหมด.

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

ฟังก์ชัน xlookup 6

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

=XLOOKUP(E2,A2:A10,C2:C10)

ฟังก์ชัน xlookup 7

ตอนนี้คุณรู้ราคาต่อหน่วยของเมาส์แล้วด้วยสูตร XLOOKUP ขั้นสูง เนื่องจากรหัสการจับคู่ได้ตั้งค่าเริ่มต้นเป็นการจับคู่แบบตรงทั้งหมด คุณไม่จำเป็นต้องระบุรหัสนั้น ง่ายกว่าและมีประสิทธิภาพมากกว่า VLOOKUP

เพียงไม่กี่คลิกเพื่อรับการจับคู่ที่แน่นอน

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

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

1. คลิกเซลล์เพื่อใส่ผลลัพธ์ที่ตรงกัน

2 ไปที่ Kutools คลิกแท็บ ตัวช่วยสูตรแลวคลิกปุ the ม ตัวช่วยสูตรในรายการแบบหล่นลง.

ฟังก์ชัน xlookup 8

3 ใน กล่องโต้ตอบตัวช่วยสูตรโปรดกำหนดค่าดังนี้:

  • เลือก ค้นหา ใน ส่วนประเภทสูตร;
  • ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร เลือกส่วนสูตรให้เลือก มองหาค่าในรายการ;
  • ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร ส่วนอินพุตอาร์กิวเมนต์โปรดดำเนินการดังนี้:
    • ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร Table_array กล่องเลือกช่วงข้อมูลที่มีค่าการค้นหาและค่าผลลัพธ์
    • ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร lookup_value กล่องให้เลือกเซลล์หรือช่วงของค่าที่คุณกำลังค้นหา โปรดทราบว่าต้องอยู่ในคอลัมน์แรกของ table_array
    • ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร กล่องคอลัมน์เลือกคอลัมน์ที่คุณจะคืนค่าที่ตรงกัน

ฟังก์ชัน xlookup 9

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

ฟังก์ชัน xlookup 10

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


ตัวอย่างที่ 2 การจับคู่โดยประมาณ

ทำการแข่งขันโดยประมาณด้วย XLOOKUP

ที่จะเรียกใช้ an การค้นหาโดยประมาณคุณจะต้อง ตั้งค่าโหมดการจับคู่เป็น 1 หรือ -1 ในอาร์กิวเมนต์ที่ห้า. เมื่อไม่พบการจับคู่แบบตรงทั้งหมด จะส่งคืนค่าที่มากกว่าหรือน้อยกว่าถัดไป.

ในกรณีนี้ คุณจำเป็นต้องทราบอัตราภาษีของรายได้ของพนักงานของคุณ ทางด้านซ้ายของสเปรดชีตคือ Federal Income Tax Brackets สำหรับปี 2021 คุณจะรับอัตราภาษีของพนักงานในคอลัมน์ E ได้อย่างไร ไม่ต้องกังวล โปรดดำเนินการดังนี้:

1. พิมพ์ ด้านล่างสูตร ลงในเซลล์ว่าง E2 แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
จากนั้นเปลี่ยนการจัดรูปแบบของผลลัพธ์ที่ส่งคืนตามที่คุณต้องการ

=XLOOKUP(D2,B2:B8,A2:A8,,1)

ฟังก์ชัน xlookup 11 >>> ฟังก์ชัน xlookup 12

√ หมายเหตุ: อาร์กิวเมนต์ที่สี่ [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
ฟังก์ชัน xlookup 13 >>> ฟังก์ชัน xlookup 14

3 แล้วก็ ลากที่จับเติมลง เพื่อให้ได้ผลลัพธ์ทั้งหมด

ฟังก์ชัน xlookup 15

√ หมายเหตุ:

  • กดปุ่ม 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 แห่ง และต้องการทราบมูลค่าตามราคาตลาดของบริษัทสองสามแห่ง แต่ชื่อบริษัทนั้นสั้น นี่คือสถานการณ์สมมติที่สมบูรณ์แบบสำหรับการจับคู่สัญลักษณ์แทน โปรดปฏิบัติตามฉันทีละขั้นตอนเพื่อทำเคล็ดลับ

ฟังก์ชัน xlookup 16

√ หมายเหตุ: หากต้องการจับคู่สัญลักษณ์แทน สิ่งสำคัญที่สุดคือตั้งค่าอาร์กิวเมนต์ที่ห้า [match_mode] เป็น 2

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

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

ฟังก์ชัน xlookup 17 >>> ฟังก์ชัน xlookup 18

2. ตอนนี้คุณรู้ผลลัพธ์ของเซลล์ H3 แล้ว เพื่อให้ได้ผลลัพธ์ที่เหลือ คุณต้อง ทำให้ lookup_array และ return_array คงที่ โดยวางเคอร์เซอร์ในอาร์เรย์แล้วกดปุ่ม F4 จากนั้นสูตรใน H3 จะกลายเป็น:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. ลากที่จับเติมลง เพื่อให้ได้ผลลัพธ์ทั้งหมด

ฟังก์ชัน xlookup 19

√ หมายเหตุ:

  • lookup_value ของสูตรในเซลล์ H3 คือ "*"&G3&"*" เรา เชื่อมเครื่องหมายดอกจัน (*) กับค่า G3 โดยใช้โปรแกรม เครื่องหมาย (&).
  • อาร์กิวเมนต์ที่สี่ [If_not_found] เป็นทางเลือก ฉันก็เลยข้ามไป
ตัวอย่างที่ 4: มองไปทางซ้าย

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

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

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

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

ฟังก์ชัน xlookup 20

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

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

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

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

ฟังก์ชัน xlookup 21

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

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

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

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

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

ฟังก์ชัน xlookup 22

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

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

ฟังก์ชัน xlookup 23

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

ฟังก์ชัน xlookup 24

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


ตัวอย่างที่ 5: ค้นหาแนวตั้งหรือแนวนอน

ดำเนินการค้นหาแนวตั้งหรือแนวนอนด้วย XLOOKUP

ในฐานะผู้ใช้ Excel คุณอาจคุ้นเคยกับฟังก์ชัน VLOOKUP และ HLOOKUP VLOOKUP คือการมองในแนวตั้งในคอลัมน์ และ HLOOKUP คือการมองในแนวนอนเป็นแถว.

XLOOKUP ใหม่รวมทั้งสองอย่างเข้าด้วยกัน, หมายความว่า คุณจำเป็นต้องใช้ไวยากรณ์เดียวเพื่อทำการค้นหาแนวตั้งหรือการค้นหาแนวนอน. อัจฉริยะใช่มั้ย?

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

เพื่อทำการค้นหาแนวตั้ง, พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง E2 ให้กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=XLOOKUP(E1,A2:A13,B2:B13)

ฟังก์ชัน xlookup 25

เพื่อทำการค้นหาแนวนอน, พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง P2 ให้กด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=XLOOKUP(P1,B1:M1,B2:M2)

ฟังก์ชัน xlookup 26

ที่คุณสามารถดู, ไวยากรณ์เหมือนกัน. ความแตกต่างเท่านั้น ระหว่างสองสูตรคือคุณป้อน คอลัมน์ ในการค้นหาแนวตั้งในขณะที่คุณป้อน แถว ในการค้นหาแนวนอน

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

ทำการค้นหาแบบสองทางด้วย XLOOKUP

คุณยังใช้อยู่หรือเปล่า ฟังก์ชัน INDEX และ MATCH เพื่อค้นหาค่าในช่วงสองมิติ? ลอง XLOOKUP . ที่ปรับปรุงแล้ว เพื่อให้งานของคุณเสร็จสิ้น ง่ายขึ้น.

XLOOKUP สามารถทำได้ ค้นหาสองครั้ง, หา การตัด ของสองค่า โดย การทำรัง XLOOKUP หนึ่งอันในอีกอันหนึ่ง XLOOKUP ด้านในสามารถส่งคืนทั้งแถวหรือทั้งคอลัมน์ จากนั้นแถวหรือคอลัมน์ที่ส่งคืนนี้จะถูกป้อนลงใน XLOOKUP ภายนอกเป็นอาร์เรย์ส่งคืน

สมมติว่าคุณมีรายชื่อเกรดของนักเรียนที่มีสาขาวิชาต่างกัน คุณต้องการทราบเกรดของวิชาเคมีของคิม

ฟังก์ชัน xlookup 43

เรามาดูกันว่าเราใช้ 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 ให้กด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์

ฟังก์ชัน xlookup 27

หรือจะทำก็ได้ วิธีอื่น ๆให้ใช้ "inner" XLOOKUP เพื่อคืนค่าของทั้งแถว ซึ่งเป็นเกรดหัวเรื่องทั้งหมดของ Kim จากนั้นใช้ "ชั้นนอก" XLOOKUP เพื่อค้นหาเกรดเคมีจากคะแนนวิชาทั้งหมดของ Kim

    • พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง H4 แล้วกด เข้าสู่ ปุ่มเพื่อรับผลลัพธ์

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

ฟังก์ชัน xlookup 28

ฟังก์ชันการค้นหาแบบสองทางของ 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")

ฟังก์ชัน xlookup 29

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

หากต้องการแทนที่ข้อผิดพลาด #N/A ด้วยข้อความที่คุณกำหนดเองอย่างรวดเร็ว Kutools สำหรับ Excel is เครื่องมือที่สมบูรณ์แบบ ใน Excel เพื่อช่วยคุณ ด้วยบิวด์อิน แทนที่ 0 หรือ #N/A ด้วยคุณสมบัติค่าว่างหรือค่าเฉพาะคุณสามารถระบุข้อความที่ไม่พบได้โดยไม่ต้องใช้สูตรที่ซับซ้อนหรือเข้าถึง XLOOKUP

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

1 ไปที่ Kutools แท็บใน Excel ค้นหา สุดยอดการค้นหาและคลิก แทนที่ 0 หรือ # N / A ด้วยค่าว่างหรือค่าเฉพาะ ในรายการแบบหล่นลง

ฟังก์ชัน xlookup 30

2 ใน แทนที่ 0 หรือ #N/A ด้วยกล่องโต้ตอบค่าว่างหรือค่าเฉพาะคุณต้องกำหนดค่าดังนี้:

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

ฟังก์ชัน xlookup 31

3 คลิกที่ OK ปุ่มเพื่อรับผลลัพธ์ ข้อความที่กำหนดเองจะปรากฏขึ้นเมื่อไม่พบรายการที่ตรงกัน

ฟังก์ชัน xlookup 32

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


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

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

อื่น ความได้เปรียบ ของ XLOOKUP คือความสามารถในการ ส่งคืนค่าหลายค่า ในเวลาเดียวกันสำหรับการแข่งขันเดียวกัน ป้อนสูตรหนึ่งเพื่อให้ได้ผลลัพธ์แรก จากนั้นจึงคืนค่าอื่นๆ กระฉอก ลงในเซลล์ว่างข้างเคียงโดยอัตโนมัติ

ในตัวอย่างด้านล่าง คุณต้องการรับข้อมูลทั้งหมดเกี่ยวกับรหัสนักศึกษา “FG9940005” เคล็ดลับคือการระบุช่วงเป็น return_array ในสูตร แทนที่จะเป็นคอลัมน์หรือแถวเดียว ในกรณีนี้ ช่วงอาร์เรย์ส่งคืนคือ B2:D9 รวมถึงสามคอลัมน์

พิมพ์ ด้านล่างสูตร ในเซลล์ว่าง G2 ให้กด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ทั้งหมด

=XLOOKUP(F2,A2:A9,B2:D9)

ฟังก์ชัน xlookup 33

เซลล์ผลลัพธ์ทั้งหมดแสดงสูตรเดียวกัน คุณสามารถ แก้ไขหรือแก้ไข สูตร ในเซลล์แรกแต่ ในเซลล์อื่นๆ สูตรไม่สามารถแก้ไขได้. คุณสามารถเห็นแถบสูตรคือ เป็นสีเทาหมายความว่าไม่สามารถเปลี่ยนแปลงได้

ฟังก์ชัน xlookup 34

สรุปแล้ว ฟังก์ชันหลายค่าของ XLOOKUP คือ a การปรับปรุงที่เป็นประโยชน์ เทียบกับ VLOOKUP คุณไม่ต้องระบุหมายเลขคอลัมน์ทุกคอลัมน์แยกกันสำหรับแต่ละสูตร ยกนิ้วให้!

ตัวอย่างที่ 9 เกณฑ์หลายเกณฑ์

ทำการค้นหาแบบหลายเกณฑ์โดยใช้ XLOOKUP

อื่น คุณสมบัติใหม่ที่น่าตื่นตาตื่นใจ ของ XLOOKUP คือความสามารถในการ ค้นหาด้วยหลายเกณฑ์. เคล็ดลับคือการ concatenate ค่าการค้นหาและอาร์เรย์การค้นหาด้วย "&" โอเปอเรเตอร์ แยกกันในสูตร มาอธิบายผ่านตัวอย่างด้านล่าง

เราต้องรู้ราคาแจกันสีน้ำเงินกลาง ในกรณีนี้ ต้องใช้ค่าการค้นหาสามค่า (เกณฑ์) เพื่อค้นหาค่าที่ตรงกัน พิมพ์ สูตรด้านล่าง ในเซลล์ว่าง I2 จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

ฟังก์ชัน xlookup 35

√ หมายเหตุ: XLOOKUP สามารถประมวลผลอาร์เรย์ได้โดยตรง ไม่จำเป็นต้องยืนยันสูตรด้วย Control + Shift + Enter

ค้นหาหลายเงื่อนไขด้วยวิธีที่รวดเร็ว

มีผู้ใด เร็วขึ้นและง่ายขึ้น วิธีดำเนินการค้นหาแบบหลายเกณฑ์มากกว่า XLOOKUP ใน excel Kutools สำหรับ Excel ให้ คุณสมบัติที่น่าทึ่ง - การค้นหาแบบหลายเงื่อนไข. ด้วยคุณสมบัตินี้ คุณสามารถเรียกใช้การค้นหาเกณฑ์ได้หลายแบบด้วยการคลิกเพียงไม่กี่ครั้ง!

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

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

ฟังก์ชัน xlookup 36

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

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

ฟังก์ชัน xlookup 37

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

ฟังก์ชัน xlookup 38

√ หมายเหตุ:

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

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


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

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

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

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

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

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

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

ฟังก์ชัน xlookup 39

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

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

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

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

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

ฟังก์ชัน xlookup 40

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

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

ฟังก์ชัน xlookup 41

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

ฟังก์ชัน xlookup 42

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

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


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

ตัวอย่าง XLOOKUP.xlsx

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



เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel ช่วยแก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...
kte แท็บ 201905
  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มผลผลิตของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
ด้านล่าง officetab
จัดเรียงความคิดเห็นโดย
ความคิดเห็น (0)
ยังไม่มีการให้คะแนน เป็นคนแรกที่ให้คะแนน!
มีความคิดเห็นยังไม่มีการโพสต์ที่นี่
แสดงความคิดเห็นของคุณ
โพสต์ในฐานะแขก
×
ให้คะแนนโพสต์นี้:
0  ตัวอักษร
สถานที่แนะนำ