วิธีการใช้ฟังก์ชัน VLOOKUP ของ Excel

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

ถ้าไม่มีการจัดเรียงข้อมูล VLOOKUP อาจส่งคืนผลลัพธ์ที่ไม่ถูกต้อง

ตรงกับการจับคู่โดยประมาณ

VLOOKUP สามารถตั้งค่าเพื่อให้ได้รับข้อมูลเฉพาะที่ตรงกับ ค่าค้นหา _value หรือสามารถตั้งค่าเพื่อให้ได้ผลลัพธ์ที่ตรงกัน

ปัจจัยที่กำหนดคือ range_lookup ข้อโต้แย้ง:

• ตั้งค่าเป็น FALSE จะแสดงเฉพาะข้อมูลที่เกี่ยวข้องกับการจับคู่ที่ตรงกับ ค่าค้นหา _value
• ตั้งค่าเป็น TRUE หรือละเว้นจะแสดงข้อมูลที่ถูกต้องหรือโดยประมาณที่เกี่ยวข้องกับ ค่าค้นหา _value

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

บันทึก: VLOOKUP ไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ - ทั้งสองอย่าง วิดเจ็ต และ วิดเจ็ต เป็นตัวสะกดที่ยอมรับได้สำหรับตัวอย่างข้างต้น

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

การป้อนอาร์กิวเมนต์ของฟังก์ชัน Excel VLOOKUP โดยใช้ Pointing

ในตัวอย่างแรกภาพด้านบนสูตรต่อไปนี้ที่มีฟังก์ชัน VLOOKUP ใช้เพื่อค้นหาราคาต่อหน่วย วิดเจ็ต อยู่ในตารางข้อมูล

= VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE)

แม้ว่าสูตรนี้จะถูกพิมพ์ลงในเซลล์แผ่นงานตัวเลือกอื่นที่ใช้กับขั้นตอนด้านล่างคือการใช้กล่องโต้ตอบของฟังก์ชันดังที่แสดงด้านบนเพื่อป้อนอาร์กิวเมนต์

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

ขั้นตอนด้านล่างถูกใช้เพื่อป้อนฟังก์ชัน VLOOKUP ลงในเซลล์ B2 โดยใช้กล่องโต้ตอบของฟังก์ชัน

การเปิดกล่องโต้ตอบ VLOOKUP

1. คลิกที่เซลล์ B2 เพื่อสร้างเซลล์ที่ใช้งานอยู่ - ตำแหน่งที่แสดงผลของฟังก์ชัน VLOOKUP
2. คลิกที่ สูตร แถบ
3. เลือก ค้นหาและอ้างอิง จากริบบิ้นเพื่อเปิดรายการฟังก์ชั่นแบบหล่นลง
4. คลิกที่ VLOOKUP ในรายการเพื่อเปิดกล่องโต้ตอบของฟังก์ชั่น

ข้อมูลที่ป้อนลงในสี่แถวที่ว่างเปล่าของกล่องโต้ตอบสร้างอาร์กิวเมนต์สำหรับฟังก์ชัน VLOOKUP

ชี้ไปที่การอ้างอิงเซลล์

อาร์กิวเมนต์สำหรับฟังก์ชัน VLOOKUP ถูกป้อนลงในบรรทัดแยกจากกล่องโต้ตอบดังที่แสดงในภาพด้านบน

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

ใช้การอ้างอิงเซลล์สัมพัทธ์และสัมบูรณ์ด้วยอาร์กิวเมนต์

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

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

ในภาพด้านบนสัญญาณดอลลาร์ ( $ ) ล้อมรอบการอ้างอิงเซลล์สำหรับ table_array อาร์กิวเมนต์ระบุว่าเป็นข้อมูลอ้างอิงของเซลล์สัมบูรณ์ซึ่งหมายความว่าจะไม่เปลี่ยนแปลงหากฟังก์ชันถูกคัดลอกไปยังเซลล์อื่น

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

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

การอ้างอิงเซลล์สัมพัทธ์ทำให้สามารถค้นหาหลายรายการในตารางข้อมูลเดียวกันได้โดยการคัดลอก VLOOKUP ไปยังตำแหน่งต่างๆและป้อนข้อมูลที่ต่างกัน lookup_values .

การป้อนอาร์กิวเมนต์ของฟังก์ชัน

1. คลิกที่ ค่าค้นหา _value บรรทัดใน VLOOKUP กล่องโต้ตอบ
2. คลิกที่เซลล์ A2 ในแผ่นงานเพื่อป้อนข้อมูลอ้างอิงเซลล์นี้เป็น SEARCH_KEY ข้อโต้แย้ง
3. คลิกที่ table_array บรรทัดของกล่องโต้ตอบ
4. เน้นเซลล์ A5 เป็น B8 ในแผ่นงานเพื่อป้อนช่วงนี้เป็น table_array อาร์กิวเมนต์ - ไม่รวมส่วนหัวของตาราง
5. กด F4 บนแป้นพิมพ์เพื่อเปลี่ยนช่วงเพื่อการอ้างอิงเซลล์แบบสัมบูรณ์
6. คลิกที่ col_index_num บรรทัดของกล่องโต้ตอบ
7. พิมพ์ a 2 ในบรรทัดนี้เป็น col_index_num อาร์กิวเมนต์เนื่องจากอัตราคิดลดอยู่ในคอลัมน์ที่ 2 ของ table_array ข้อโต้แย้ง
8. คลิกที่ range_lookup บรรทัดของกล่องโต้ตอบ
9. พิมพ์คำ เท็จ เป็น range_lookup ข้อโต้แย้ง
10. กด เข้าสู่ บนแป้นพิมพ์เพื่อปิดกล่องโต้ตอบและกลับไปที่แผ่นงาน
11. คำตอบ $ 14.76 - ราคาต่อหน่วยสำหรับ Widget - ควรปรากฏในเซลล์ B2 ของแผ่นงาน
12. เมื่อคุณคลิกที่เซลล์ B2 ฟังก์ชันที่สมบูรณ์ = VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE) ปรากฏในแถบสูตรเหนือแผ่นงาน

ข้อความแสดงข้อผิดพลาด Excel VLOOKUP

ข้อความแสดงข้อผิดพลาดต่อไปนี้เกี่ยวข้องกับ VLOOKUP:

ข้อผิดพลาด # N / A ("ค่าไม่พร้อมใช้งาน") จะปรากฏขึ้นหาก:

• ค่าค้นหา _value ไม่พบในคอลัมน์แรกของ พิสัย ข้อโต้แย้ง
• table_array อาร์กิวเมนต์ไม่ถูกต้อง ตัวอย่างเช่นอาร์กิวเมนต์อาจรวมถึงคอลัมน์ว่างที่ด้านซ้ายของช่วง
• range_lookup อาร์กิวเมนต์ถูกตั้งค่าเป็น FALSE และตรงทั้งหมดสำหรับ SEARCH_KEY อาร์กิวเมนต์ไม่พบในคอลัมน์แรกของ พิสัย
• range_lookup อาร์กิวเมนต์ถูกตั้งค่าเป็น TRUE และค่าทั้งหมดในคอลัมน์แรกของ พิสัย มีขนาดใหญ่กว่า SEARCH_KEY

#REF! ข้อผิดพลาดจะปรากฏขึ้นถ้า:

• col_index_num อาร์กิวเมนต์มีค่ามากกว่าจำนวนคอลัมน์ในอาร์เรย์ของตาราง