ไปยังเนื้อหาหลัก

ฟังก์ชัน Excel VLOOKUP

ผู้เขียน : ซิลูเวีย แก้ไขล่าสุด: 2023-06-01

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


วิดีโอที่เกี่ยวข้อง


คำอธิบายทีละขั้นตอนของข้อโต้แย้ง

ดังที่แสดงในภาพด้านบน ฟังก์ชัน VLOOKUP จะใช้เพื่อค้นหาอีเมลตามหมายเลข ID ที่กำหนด ตอนนี้ฉันจะให้คำอธิบายโดยละเอียดเกี่ยวกับวิธีใช้ VLOOKUP ในตัวอย่างนี้ โดยแบ่งอาร์กิวเมนต์แต่ละรายการทีละขั้นตอน

ขั้นตอนที่ 1: เริ่มฟังก์ชัน VLOOKUP

เลือกเซลล์ (ในกรณีนี้คือ H6) เพื่อแสดงผลลัพธ์ จากนั้นเริ่มฟังก์ชัน VLOOKUP โดยพิมพ์เนื้อหาต่อไปนี้ในช่อง สูตรบาร์.

=VLOOKUP(
ขั้นตอนที่ 2: ระบุค่าการค้นหา

ขั้นแรก ระบุค่าการค้นหา (ซึ่งเป็นสิ่งที่คุณกำลังมองหา) ในฟังก์ชัน VLOOKUP ในที่นี้ ฉันอ้างอิงเซลล์ G6 ซึ่งมีหมายเลขประจำตัว 1005

=VLOOKUP(G6

หมายเหตุ: ค่าการค้นหาต้องอยู่ในคอลัมน์แรกของช่วงข้อมูล
ขั้นตอนที่ 3: ระบุอาร์เรย์ของตาราง

จากนั้น ระบุช่วงของเซลล์ที่มีทั้งค่าที่คุณต้องการและค่าที่คุณต้องการส่งคืน ในกรณีนี้ ฉันเลือกช่วง B6:E12 ตอนนี้สูตรปรากฏดังนี้:

=VLOOKUP(G6,B6:E12

หมายเหตุ: หากคุณต้องการคัดลอกฟังก์ชัน VLOOKUP เพื่อค้นหาค่าหลายค่าในคอลัมน์เดียวกันและได้ผลลัพธ์ที่แตกต่างกัน คุณต้องใช้การอ้างอิงแบบสัมบูรณ์โดยการเพิ่มเครื่องหมายดอลลาร์ เช่นนี้
=VLOOKUP(G6,$B$6:$E$12
ขั้นตอนที่ 4: ระบุคอลัมน์ที่คุณต้องการส่งคืนค่า

จากนั้นระบุคอลัมน์ที่คุณต้องการส่งคืนค่า

ในตัวอย่างนี้ เนื่องจากฉันต้องการส่งคืนอีเมลตามหมายเลข ID ฉันจึงป้อนหมายเลข 4 เพื่อบอกให้ VLOOKUP ส่งกลับค่าจากคอลัมน์ที่สี่ของช่วงข้อมูล

=VLOOKUP(G6,B6:E12,4

ขั้นตอนที่ 5: ค้นหาค่าประมาณหรือค่าที่ตรงกันทั้งหมด

สุดท้าย ให้พิจารณาว่าคุณกำลังมองหาการจับคู่แบบประมาณหรือแบบตรงทั้งหมด

  • เพื่อค้นหา an คู่ที่เหมาะสมต้องใช้ FALSE เป็นอาร์กิวเมนต์สุดท้าย
  • เพื่อค้นหา an การแข่งขันโดยประมาณ, ใช้ TRUE เป็นอาร์กิวเมนต์สุดท้าย หรือเว้นว่างไว้

ในตัวอย่างนี้ ฉันใช้ FALSE สำหรับการจับคู่แบบตรงทั้งหมด ตอนนี้สูตรมีลักษณะดังนี้:

=VLOOKUP(G6,B6:E12,4,FALSE

กดปุ่ม Enter เพื่อรับผลลัพธ์

ด้วยการอธิบายแต่ละอาร์กิวเมนต์ทีละรายการในตัวอย่างข้างต้น ไวยากรณ์และอาร์กิวเมนต์ของฟังก์ชัน VLOOKUP จะเข้าใจได้ง่ายขึ้นมาก


ไวยากรณ์และอาร์กิวเมนต์

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

  • lookup_value (จำเป็น): ค่า (ค่าจริงหรือการอ้างอิงเซลล์) ที่คุณต้องการ จำไว้ว่าค่านี้ต้องอยู่ในคอลัมน์แรกของ table_array
  • Table_array (จำเป็น): ช่วงของเซลล์มีทั้งคอลัมน์ของค่าที่ค้นหาและคอลัมน์ของค่าที่ส่งกลับ
  • Col_index (จำเป็น): จำนวนเต็มแสดงถึงหมายเลขคอลัมน์ที่มีค่าส่งคืน เริ่มต้นด้วยหมายเลข 1 สำหรับคอลัมน์ซ้ายสุดของ table_array
  • ช่วง_lookup (ไม่บังคับ): ค่าตรรกะที่กำหนดว่าคุณต้องการให้ VLOOKUP ค้นหาค่าที่ตรงกันโดยประมาณหรือค่าที่ตรงกันทุกประการ
    • การจับคู่โดยประมาณ - ตั้งค่าอาร์กิวเมนต์นี้เป็น TRUE, 1 หรือทิ้งไว้ ว่างเปล่า.
      สำคัญ: หากต้องการค้นหาค่าที่ตรงกัน ค่าในคอลัมน์แรกของ table_array จะต้องเรียงลำดับจากน้อยไปหามาก ในกรณีที่ VLOOKUP ส่งกลับผลลัพธ์ที่ไม่ถูกต้อง
    • คู่ที่เหมาะสม - ตั้งค่าอาร์กิวเมนต์นี้เป็น FALSE or 0.

ตัวอย่าง

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

ตัวอย่างที่ 1: การจับคู่แบบตรงทั้งหมดกับการจับคู่โดยประมาณใน VLOOKUP

หากคุณสับสนเกี่ยวกับการจับคู่แบบตรงทั้งหมดและการจับคู่โดยประมาณเมื่อใช้ VLOOKUP ส่วนนี้สามารถช่วยให้คุณคลายความสับสนได้

ตรงกันทุกประการใน VLOOKUP

ในตัวอย่างนี้ ฉันจะค้นหาชื่อที่เกี่ยวข้องตามคะแนนที่แสดงในช่วง E6:E8 ดังนั้นฉันจึงป้อนสูตรต่อไปนี้ในเซลล์ F6 และลากตัวจัดการป้อนอัตโนมัติลงไปที่ F8 ในสูตรนี้ อาร์กิวเมนต์สุดท้ายถูกระบุเป็น FALSE เพื่อทำการค้นหาการจับคู่แบบตรงทั้งหมด

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

อย่างไรก็ตาม เนื่องจากไม่มีคะแนน 98 ในคอลัมน์แรกของช่วงข้อมูล VLOOKUP จะส่งกลับผลลัพธ์ข้อผิดพลาด #N/A

หมายเหตุ: ที่นี่ฉันล็อกอาร์เรย์ตาราง ($B$6:$C$12) ในฟังก์ชัน VLOOKUP เพื่ออ้างอิงอย่างรวดเร็ว คงเส้นคงวา ชุดข้อมูลเทียบกับค่าการค้นหาหลายค่า
การจับคู่โดยประมาณใน VLOOKUP

ยังคงใช้ตัวอย่างด้านบน หากคุณเปลี่ยนอาร์กิวเมนต์สุดท้ายเป็น TRUE, VLOOKUP จะทำการค้นหาการจับคู่โดยประมาณ หากไม่พบค่าที่ตรงกัน ระบบจะค้นหาค่าที่มากที่สุดถัดไปซึ่งน้อยกว่าค่าที่ค้นหา และส่งกลับผลลัพธ์ที่เกี่ยวข้อง

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

เนื่องจากไม่มีคะแนน 98 VLOOKUP จึงค้นหาค่าที่มากที่สุดถัดไปที่น้อยกว่า 98 ซึ่งก็คือ 95 และส่งกลับชื่อคะแนน 95 เป็นผลลัพธ์ที่ใกล้เคียงที่สุด

หมายเหตุ : :
  • ในกรณีการจับคู่โดยประมาณนี้ ค่าในคอลัมน์แรกของ table_array จะต้องเรียงลำดับจากน้อยไปหามาก มิฉะนั้น VLOOKUP อาจไม่ส่งคืนค่าที่ถูกต้อง
  • ที่นี่ฉันล็อกอาร์เรย์ของตาราง ($B$6:$C$12) ในฟังก์ชัน VLOOKUP เพื่ออ้างอิงชุดข้อมูลที่สอดคล้องกันอย่างรวดเร็วเทียบกับค่าการค้นหาหลายค่า

ตัวอย่างที่ 2: ใช้ VLOOKUP ที่มีหลายเกณฑ์

ส่วนนี้สาธิตวิธีใช้ VLOOKUP กับหลายเงื่อนไขใน Excel ดังที่แสดงในภาพหน้าจอด้านล่าง หากคุณกำลังพยายามหาเงินเดือนตามชื่อที่ระบุ (ในเซลล์ H5) และแผนก (ในเซลล์ H6) ให้ทำตามขั้นตอนด้านล่างเพื่อดำเนินการให้เสร็จสิ้น

ขั้นตอนที่ 1: เพิ่มคอลัมน์ตัวช่วยเพื่อเชื่อมค่าจากคอลัมน์การค้นหาเข้าด้วยกัน

ในกรณีนี้ เราจำเป็นต้องสร้างคอลัมน์ตัวช่วยเพื่อเชื่อมค่าจาก Name คอลัมน์และ แผนก คอลัมน์.

  1. เพิ่มคอลัมน์ตัวช่วยทางด้านซ้ายของช่วงข้อมูลและระบุส่วนหัวของคอลัมน์นี้ ดูภาพหน้าจอ:
  2. ในคอลัมน์ตัวช่วยนี้ เลือกเซลล์แรกใต้ส่วนหัว ป้อนสูตรต่อไปนี้ใน แถบสูตรและกด เข้าสู่.
    =C6&" "&D6
    หมายเหตุ : : ในสูตรนี้ เราใช้เครื่องหมายและ (&) เพื่อรวมข้อความในสองคอลัมน์เพื่อสร้างข้อความชิ้นเดียว
    • C6 เป็นชื่อแรกของ Name คอลัมน์ที่จะเข้าร่วม D6 เป็นแผนกแรกของ แผนก คอลัมน์ที่จะเข้าร่วม
    • ค่าของเซลล์ทั้งสองนี้เชื่อมกันด้วยช่องว่างระหว่าง
  3. เลือกเซลล์ผลลัพธ์นี้ จากนั้นลาก ที่จับการป้อนอัตโนมัติ ลงเพื่อใช้สูตรนี้กับเซลล์อื่นในคอลัมน์เดียวกัน
ขั้นตอนที่ 2: ใช้ฟังก์ชัน VLOOKUP กับเกณฑ์ที่กำหนด

เลือกเซลล์ที่คุณต้องการแสดงผลลัพธ์ (ในที่นี้ฉันเลือก I7) ป้อนสูตรต่อไปนี้ใน แถบสูตรจากนั้นกด เข้าสู่.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
ผล

หมายเหตุ : :
  • ต้องใช้คอลัมน์ตัวช่วยเป็นคอลัมน์แรกของช่วงข้อมูล
  • ตอนนี้คอลัมน์เงินเดือนเป็นคอลัมน์ที่ห้าของช่วงข้อมูล เราจึงใช้ตัวเลขนี้ 5 เป็นดัชนีคอลัมน์ในสูตร
  • เราต้องเข้าร่วมเกณฑ์ใน I5 และ I6 (I5& " "&I6) แบบเดียวกับคอลัมน์ตัวช่วยและใช้ค่าที่ต่อกันเป็น lookup_value อาร์กิวเมนต์ในสูตร
  • คุณยังสามารถใส่เงื่อนไขทั้งสองโดยตรงในอาร์กิวเมนต์ lookup_value และคั่นด้วยช่องว่าง (หากเงื่อนไขเป็นข้อความ อย่าลืมใส่เครื่องหมายคำพูดคู่)
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • ทางเลือกที่ดีกว่า - ค้นหาด้วยหลายเกณฑ์ในไม่กี่วินาที
    พื้นที่ การค้นหาแบบหลายเงื่อนไข คุณลักษณะของ Kutools สำหรับ Excel สามารถช่วยให้คุณค้นหาด้วยหลายเกณฑ์ได้อย่างง่ายดายในไม่กี่วินาที ทดลองใช้ฟีเจอร์เต็มรูปแบบฟรี 30 วันทันที!

ข้อผิดพลาดทั่วไปของ VLOOKUP และแนวทางแก้ไข

ส่วนนี้แสดงรายการข้อผิดพลาดทั่วไปที่คุณอาจพบเมื่อใช้ VLOOKUP และนำเสนอวิธีแก้ไข

  ภาพรวมของข้อผิดพลาดทั่วไปของ VLOOKUP:
          
         เหตุผลที่ 1: ค่าการค้นหาไม่ได้อยู่ในคอลัมน์แรก  
     เหตุผลที่ 2: ไม่พบค่าการค้นหา  
  ------  เหตุผลที่ 3: ค่าการค้นหาน้อยกว่าค่าที่น้อยที่สุด  
     เหตุผลที่ 4: ตัวเลขถูกจัดรูปแบบเป็นข้อความ  
       เหตุผลที่ 5: Table_array ไม่คงที่  
         
  ------  เหตุผลที่ 1: ค่าการค้นหาเกิน 255 อักขระ  
   เหตุผลที่ 2: Col_index น้อยกว่า 1  
         
  ------  เหตุผลที่ 1: Col_index มากกว่าจำนวนคอลัมน์  
   
         
  ------  เหตุผลที่ 1: คอลัมน์การค้นหาไม่ได้เรียงลำดับจากน้อยไปหามาก  
   เหตุผลที่ 2: มีการแทรกหรือลบคอลัมน์  
         

ข้อผิดพลาด #N/A กำลังส่งคืน

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

เหตุผลที่ 1: ค่าการค้นหาไม่ได้อยู่ในคอลัมน์แรกของ table_array

ข้อจำกัดอย่างหนึ่งของ Excel VLOOKUP คืออนุญาตให้คุณมองจากซ้ายไปขวาเท่านั้น ดังนั้น ค่าการค้นหาจะต้องอยู่ในคอลัมน์แรกของ table_array

ดังที่แสดงในภาพด้านล่าง ฉันต้องการคืนชื่อตามตำแหน่งงานที่กำหนด นี่คือค่าการค้นหา (ผู้จัดการฝ่ายขาย) อยู่ในคอลัมน์ที่สองของ table_array และค่าส่งคืนจะอยู่ทางซ้ายของคอลัมน์ค้นหา ดังนั้น VLOOKUP จึงส่งกลับข้อผิดพลาด #N/A

โซลูชัน

คุณสามารถใช้วิธีแก้ปัญหาต่อไปนี้เพื่อแก้ไขข้อผิดพลาดนี้

  • จัดเรียงคอลัมน์ใหม่
    คุณสามารถจัดเรียงคอลัมน์ใหม่เพื่อวางคอลัมน์ค้นหาในคอลัมน์แรกของ table_array
  • ใช้ฟังก์ชัน INDEX และ MATCH ร่วมกัน
    ในที่นี้เราใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันแทน VLOOKUP เพื่อแก้ปัญหานี้
    =INDEX(B6:B12,MATCH(F6,C6:C12,0))
  • ใช้ฟังก์ชัน XLOOKUP (พร้อมใช้งานใน Excel 365, Excel 2021 และรุ่นที่ใหม่กว่า)
    =XLOOKUP(F6,C6:C12,B6:B12)

เหตุผลที่ 2: ไม่พบค่าการค้นหาในคอลัมน์การค้นหา (ตรงทั้งหมด)

หนึ่งในสาเหตุที่พบบ่อยที่สุดที่ทำให้ VLOOKUP ส่งกลับข้อผิดพลาด #N/A เป็นเพราะไม่พบค่าที่คุณต้องการ

ดังที่แสดงในตัวอย่างด้านล่าง เราจะค้นหาชื่อตามคะแนน 98 ที่กำหนดใน E6 อย่างไรก็ตาม คะแนนนี้ไม่มีอยู่ในคอลัมน์แรกของช่วงข้อมูล ดังนั้น VLOOKUP จึงแสดงผลลัพธ์ข้อผิดพลาด #N/A

โซลูชัน

หากต้องการแก้ไขข้อผิดพลาดนี้ คุณสามารถลองใช้วิธีใดวิธีหนึ่งต่อไปนี้

  • หากคุณต้องการให้ VLOOKUP ค้นหาค่าที่มากที่สุดถัดไปซึ่งน้อยกว่าค่าการค้นหา ให้เปลี่ยนอาร์กิวเมนต์สุดท้าย FALSE (ตรงเป๊ะ)ถึง TRUE (การแข่งขันโดยประมาณ). สำหรับข้อมูลเพิ่มเติมโปรดดู ตัวอย่างที่ 1: การจับคู่แบบตรงทั้งหมดกับการจับคู่โดยประมาณโดยใช้ VLOOKUP.
  • หากต้องการหลีกเลี่ยงการเปลี่ยนอาร์กิวเมนต์สุดท้ายและรับการเตือนในกรณีที่ไม่พบค่าการค้นหา คุณสามารถปิดฟังก์ชัน VLOOKUP ภายในฟังก์ชัน IFERROR:
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

เหตุผลที่ 3: ค่าการค้นหาน้อยกว่าค่าที่น้อยที่สุดในคอลัมน์การค้นหา (ค่าที่ตรงกันโดยประมาณ)

ดังที่แสดงในภาพด้านล่าง คุณกำลังค้นหาการจับคู่โดยประมาณ ค่าที่คุณกำลังค้นหา (หมายเลข ID 1001 ในกรณีนี้) น้อยกว่าค่าที่น้อยที่สุด 1002 ในคอลัมน์การค้นหา ดังนั้น VLOOKUP จะส่งกลับข้อผิดพลาด #N/A

โซลูชัน

นี่คือสองวิธีแก้ปัญหาสำหรับคุณ

  • ตรวจสอบให้แน่ใจว่าค่าการค้นหามากกว่าหรือเท่ากับค่าที่น้อยที่สุดในคอลัมน์การค้นหา
  • หากคุณต้องการให้ Excel เตือนคุณว่าไม่พบค่าการค้นหา ให้ซ้อนฟังก์ชัน VLOOKUP ไว้ในฟังก์ชัน IFERROR ดังนี้:
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

เหตุผลที่ 4: ตัวเลขถูกจัดรูปแบบเป็นข้อความ

ดังที่คุณเห็นในภาพหน้าจอด้านล่าง ผลลัพธ์ข้อผิดพลาด #N/A ในตัวอย่างนี้เกิดจากชนิดข้อมูลไม่ตรงกันระหว่างเซลล์ค้นหา (G6) และคอลัมน์ค้นหา (B6:B12) ของตารางต้นฉบับ ค่าใน G6 จะเป็นตัวเลข และค่าในช่วง B6:B12 จะเป็นตัวเลขในรูปแบบข้อความ

ปลาย: ถ้าตัวเลขถูกแปลงเป็นข้อความ สามเหลี่ยมสีเขียวเล็กๆ จะแสดงที่มุมซ้ายบนของเซลล์

โซลูชัน

ในการแก้ปัญหานี้ คุณต้องแปลงค่าการค้นหากลับเป็นตัวเลข นี่คือสองวิธีสำหรับคุณ

  • ใช้คุณสมบัติแปลงเป็นตัวเลข
    คลิกที่เซลล์ที่คุณต้องการแปลงข้อความเป็นตัวเลข เลือกปุ่มนี้  ข้างเซลล์แล้วเลือก แปลงเป็นตัวเลข.
  • ใช้เครื่องมือที่มีประโยชน์เพื่อแปลงเป็นชุดระหว่างข้อความและตัวเลข
    พื้นที่ แปลงระหว่างข้อความและตัวเลข คุณลักษณะของ Kutools สำหรับ Excel ช่วยให้คุณแปลงช่วงของเซลล์จากข้อความเป็นตัวเลขและในทางกลับกันได้อย่างง่ายดาย ทดลองใช้ฟีเจอร์เต็มรูปแบบฟรี 30 วันทันที!

เหตุผลที่ 5: table_array ไม่คงที่เมื่อลากสูตร VLOOKUP ไปยังเซลล์อื่น

ดังที่แสดงในภาพหน้าจอด้านล่าง มีค่าการค้นหาสองค่าใน E6 และ E7 หลังจากได้ผลลัพธ์แรกใน F6 แล้ว ให้ลากสูตร VLOOKUP จากเซลล์ F6 ไปที่ F7 ซึ่งจะแสดงผลลัพธ์ข้อผิดพลาด #N/A นั่นเป็นเพราะการอ้างอิงเซลล์ (B6:C12) เป็นแบบสัมพัทธ์ตามค่าเริ่มต้น และปรับเปลี่ยนเมื่อคุณเลื่อนลงมาตามแถวต่างๆ อาร์เรย์ของตารางถูกย้ายลงไปที่ B7:C13 ซึ่งไม่มีคะแนนการค้นหา 73 อีกต่อไป

Solution

คุณต้องล็อคอาร์เรย์ตารางเพื่อให้คงที่โดยเพิ่ม $ ลงชื่อก่อนแถวและคอลัมน์ในการอ้างอิงเซลล์ หากต้องการทราบข้อมูลเพิ่มเติมเกี่ยวกับการอ้างอิงแบบสัมบูรณ์ใน Excel โปรดดูบทช่วยสอนนี้: การอ้างอิงแบบสัมบูรณ์ของ Excel (วิธีทำและใช้งาน).

ข้อผิดพลาด #VALUE ถูกส่งคืน

เงื่อนไขต่อไปนี้อาจทำให้ VLOOKUP ส่งคืนผลลัพธ์ข้อผิดพลาด #VALUE

เหตุผลที่ 1: ค่าการค้นหาเกิน 255 อักขระ

ดังที่แสดงในภาพหน้าจอด้านล่าง ค่าการค้นหาในเซลล์ H4 เกิน 255 อักขระ ดังนั้น VLOOKUP จะส่งกลับผลลัพธ์ข้อผิดพลาด #VALUE

โซลูชัน

เพื่อหลีกเลี่ยงข้อจำกัดนี้ คุณสามารถใช้ฟังก์ชันการค้นหาอื่นที่สามารถจัดการสตริงที่ยาวขึ้นได้ ลองใช้สูตรใดสูตรหนึ่งต่อไปนี้

  • ดัชนีและการจับคู่:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
  • ฟังก์ชัน XLOOKUP (พร้อมใช้งานใน Excel 365, Excel 2021 และเวอร์ชันที่ใหม่กว่า):
    =XLOOKUP(H4,B5:B11,E5:E11)

เหตุผลที่ 2: อาร์กิวเมนต์ col_index น้อยกว่า 1

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

หากคุณป้อนดัชนีคอลัมน์ที่น้อยกว่า 1 (เช่น ศูนย์หรือค่าลบ) VLOOKUP จะไม่สามารถระบุตำแหน่งคอลัมน์ในอาร์เรย์ของตารางได้

Solution

ในการแก้ไขปัญหานี้ ตรวจสอบให้แน่ใจว่าอาร์กิวเมนต์ดัชนีคอลัมน์ในสูตร VLOOKUP ของคุณเป็นจำนวนบวกที่สอดคล้องกับคอลัมน์ที่ถูกต้องในอาร์เรย์ของตาราง

ข้อผิดพลาด #REF กำลังส่งคืน

ส่วนนี้แสดงสาเหตุหนึ่งที่ทำให้ VLOOKUP ส่งกลับข้อผิดพลาด #REF และเสนอวิธีแก้ไขปัญหานี้

เหตุผล: อาร์กิวเมนต์ col_index มากกว่าจำนวนคอลัมน์

ดังที่คุณเห็นในภาพหน้าจอด้านล่าง อาร์เรย์ของตารางมีเพียง 4 คอลัมน์เท่านั้น อย่างไรก็ตาม ดัชนีคอลัมน์ที่คุณระบุในสูตร VLOOKUP คือ 5 ซึ่งมากกว่าจำนวนคอลัมน์ในอาร์เรย์ของตาราง ด้วยเหตุนี้ VLOOKUP จะไม่สามารถระบุตำแหน่งคอลัมน์ได้ และจะส่งกลับข้อผิดพลาด #REF ในท้ายที่สุด

โซลูชัน

  • ระบุหมายเลขคอลัมน์ที่ถูกต้อง
    ตรวจสอบให้แน่ใจว่าอาร์กิวเมนต์ดัชนีคอลัมน์ในสูตร VLOOKUP เป็นตัวเลขที่สอดคล้องกับคอลัมน์ที่ถูกต้องในอาร์เรย์ของตาราง
  • รับหมายเลขคอลัมน์โดยอัตโนมัติตามส่วนหัวของคอลัมน์ที่ระบุ
    ถ้าตารางมีหลายคอลัมน์ คุณอาจมีปัญหาในการกำหนดหมายเลขดัชนีคอลัมน์ที่ถูกต้อง ที่นี่ คุณสามารถซ้อนฟังก์ชัน MATCH ในฟังก์ชัน VLOOKUP เพื่อค้นหาตำแหน่งของคอลัมน์ตามส่วนหัวของคอลัมน์
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    หมายเหตุ: ในสูตรข้างต้น, the MATCH("อีเมล",B5:E5, 0) ฟังก์ชันใช้เพื่อรับหมายเลขคอลัมน์ของ "อีเมล" คอลัมน์ในช่วงวันที่ B6:E12 ผลลัพธ์ที่ได้คือ 4 ซึ่งใช้เป็น col_index ในฟังก์ชัน VLOOKUP

กำลังส่งคืนค่าที่ไม่ถูกต้อง

หากคุณพบว่า VLOOKUP ไม่ส่งคืนผลลัพธ์ที่ถูกต้อง อาจเกิดจากสาเหตุต่อไปนี้

เหตุผลที่ 1: คอลัมน์การค้นหาไม่ได้เรียงลำดับจากน้อยไปหามาก

หากคุณตั้งค่าอาร์กิวเมนต์สุดท้ายเป็น TRUE (หรือ ปล่อยให้มันว่างเปล่า) สำหรับการจับคู่โดยประมาณ และคอลัมน์การค้นหาไม่ได้เรียงลำดับจากน้อยไปหามาก ค่าผลลัพธ์อาจไม่ถูกต้อง

Solution

การเรียงลำดับคอลัมน์การค้นหาจากน้อยไปหามากสามารถช่วยคุณแก้ปัญหานี้ได้ ในการทำเช่นนี้ โปรดทำตามขั้นตอนด้านล่าง:

  1. เลือกเซลล์ข้อมูลในคอลัมน์ค้นหา ไปที่ ข้อมูล คลิกแท็บ เรียงลำดับจากน้อยที่สุดไปหามากที่สุด ใน จัดเรียงและกรอง กลุ่ม
  2. ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร คำเตือนการเรียงลำดับ กล่องโต้ตอบเลือกไฟล์ ขยายส่วนที่เลือก แล้วคลิก OK.

เหตุผลที่ 2: มีการแทรกหรือลบคอลัมน์

ดังที่แสดงในภาพหน้าจอด้านล่าง ค่าเดิมที่ฉันต้องการส่งคืนอยู่ในคอลัมน์ที่สี่ของอาร์เรย์ตาราง ดังนั้นฉันจึงระบุหมายเลข col_index เป็น 4 เมื่อแทรกคอลัมน์ใหม่ คอลัมน์ผลลัพธ์จะกลายเป็นคอลัมน์ที่ห้าของตาราง อาร์เรย์ ทำให้ VLOOKUP คืนผลลัพธ์จากคอลัมน์ที่ไม่ถูกต้อง

โซลูชัน

นี่คือสองวิธีแก้ปัญหาสำหรับคุณ

  • คุณสามารถเปลี่ยนหมายเลขดัชนีคอลัมน์ให้ตรงกับตำแหน่งของคอลัมน์ส่งคืนได้ด้วยตนเอง สูตรที่นี่ควรเปลี่ยนเป็น:
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • หากคุณต้องการส่งคืนผลลัพธ์จากคอลัมน์ certian เสมอ เช่น คอลัมน์อีเมลในตัวอย่างนี้ สูตรต่อไปนี้สามารถช่วยจับคู่ดัชนีคอลัมน์โดยอัตโนมัติตามส่วนหัวของคอลัมน์ที่กำหนด โดยไม่คำนึงว่าคอลัมน์จะถูกแทรกหรือลบออกจากอาร์เรย์ของตารางหรือไม่
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

หมายเหตุฟังก์ชันอื่นๆ

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

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations