ฟังก์ชัน VLOOKUP

เคล็ดลับ: ลองใช้ฟังก์ชัน XLOOKUP ใหม่ ซึ่งเป็นเวอร์ชันที่ได้รับการปรับปรุงของ VLOOKUP ที่ทำงานในทุกทิศทางและส่งกลับรายการที่ตรงกันตามค่าเริ่มต้น ทำให้ใช้งานง่ายและสะดวกมากกว่ารุ่นก่อน

ใช้ VLOOKUP เมื่อคุณต้องการค้นหาสิ่งต่างๆ ในตารางหรือช่วงทีละแถว ตัวอย่างเช่น ค้นหาราคาของชิ้นส่วนรถยนต์ตามหมายเลขชิ้นส่วน หรือค้นหาชื่อพนักงานตามรหัสพนักงาน

ในรูปแบบที่ง่ายที่สุด ฟังก์ชัน VLOOKUP จะระบุว่า:

=VLOOKUP(สิ่งที่คุณต้องการค้นหา ที่คุณต้องการค้นหา หมายเลขคอลัมน์ในช่วงที่มีค่าที่จะส่งกลับ ส่งกลับค่าที่ตรงกันโดยประมาณหรือค่าที่ตรงกันพอดี ซึ่งระบุเป็น 1/TRUE หรือ 0/FALSE)

เบราว์เซอร์ของคุณไม่สนับสนุนวิดีโอ ติดตั้ง Microsoft Silverlight, Adobe Flash Player หรือ Internet Explorer 9

เคล็ดลับ: ความลับของ VLOOKUP คือการจัดระเบียบข้อมูลของคุณ เพื่อให้ค่าที่คุณค้นหา (ผลไม้) อยู่ทางด้านซ้ายของค่าที่ส่งกลับ (จำนวน) ที่คุณต้องการค้นหา

ใช้ฟังก์ชัน VLOOKUP เพื่อหาค่าในตาราง

ไวยากรณ์

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

ตัวอย่างเช่น

=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP("กิจจาการ",B2:E7,2,FALSE)
=VLOOKUP(A2,'รายละเอียดไคลเอ็นต์'! A:F,3,FALSE)

ชื่ออาร์กิวเมนต์	คำอธิบาย
lookup_value (จำเป็น)	ค่าที่คุณต้องการค้นหา ค่าที่คุณต้องการค้นหาต้องอยู่ในคอลัมน์แรกของช่วงของเซลล์ที่คุณระบุในอาร์กิวเมนต์ *table_array* ตัวอย่างเช่น ถ้า *table-array* ครอบคลุมเซลล์ B2:D7 แล้ว lookup_value ของคุณจะต้องอยู่ในคอลัมน์ B *Lookup_value* อาจเป็นค่าหรือการอ้างอิงไปยังเซลล์ก็ได้
table_array (จำเป็น)	ช่วงของเซลล์ที่ VLOOKUP จะค้นหา *lookup_value* และส่งกลับค่า คุณสามารถใช้ช่วงที่มีชื่อหรือตาราง และคุณสามารถใช้ชื่อในอาร์กิวเมนต์แทนการอ้างอิงเซลล์ได้ คอลัมน์แรกในช่วงของเซลล์ต้องมี *lookup_value* ช่วงของเซลล์ยังต้องรวมค่าที่ส่งกลับที่คุณต้องการค้นหาด้วย เรียนรู้วิธีเลือกช่วงในเวิร์กชีต
col_index_num (จำเป็น)	หมายเลขคอลัมน์ (เริ่มต้นด้วย 1 สําหรับคอลัมน์ซ้ายสุดของ *table_array*) ที่มีค่าส่งกลับ
range_lookup (มีหรือไม่มีก็ได้)	ค่าตรรกะที่ระบุว่าคุณต้องการให้ VLOOKUP ค้นหาค่าที่ตรงกันโดยประมาณหรือค่าที่ตรงกันพอดี: ค่าที่ตรงกันโดยประมาณ - 1/TRUE จะถือว่าคอลัมน์แรกในตารางถูกเรียงลําดับตามตัวเลขหรือตามตัวอักษร แล้วจะค้นหาค่าที่ใกล้เคียงที่สุด นี่เป็นวิธีเริ่มต้นถ้าคุณไม่ได้ระบุไว้ ตัวอย่างเช่น =VLOOKUP(90,A1:B100,2,TRUE) ค่าที่ตรงกันพอดี - 0/FALSE จะค้นหาค่าที่แน่นอนในคอลัมน์แรก ตัวอย่างเช่น =VLOOKUP("Smith",A1:B100,2,FALSE)

เริ่มต้นอย่างไร

มีข้อมูลสี่อย่างที่คุณจำเป็นต้องมีเพื่อที่จะสร้างไวยากรณ์ VLOOKUP:

ค่าที่คุณต้องการค้นหา หรือที่เรียกว่าค่าการค้นหา
ช่วงที่ค่าการค้นหาอยู่ โปรดจําไว้ว่าค่าการค้นหาควรอยู่ในคอลัมน์แรกในช่วงเพื่อให้ VLOOKUP ทํางานได้อย่างถูกต้อง ตัวอย่างเช่น ถ้าค่าการค้นหาของคุณอยู่ในเซลล์ C2 ช่วงของคุณควรเริ่มต้นด้วย C
หมายเลขคอลัมน์ในช่วงที่มีค่าที่ส่งกลับ ตัวอย่างเช่น ถ้าคุณระบุให้ B2:D11 เป็นช่วง คุณควรนับ B เป็นคอลัมน์แรก C เป็นคอลัมน์ที่สอง ไปเรื่อยๆ
อีกทางหนึ่งคือ คุณสามารถระบุ TRUE ถ้าคุณต้องการค่าที่ตรงกันโดยประมาณหรือ FALSE ถ้าคุณต้องการค่าที่ตรงกันพอดีกับค่าที่ส่งกลับ ถ้าคุณไม่ได้ระบุอะไรเลย ค่าเริ่มต้นจะเป็น TRUE หรือค่าที่ตรงกันโดยประมาณเสมอ

ในตอนนี้ ให้นำค่าทั้งหมดทางด้านบนมารวมกันตามตัวอย่างทางด้านล่าง:

=VLOOKUP(ค่าการค้นหา, ช่วงที่มีค่าการค้นหา, หมายเลขคอลัมน์ในช่วงที่มีค่าส่งกลับ, ค่าที่ตรงกันโดยประมาณ (TRUE) หรือค่าที่ตรงกันพอดี (FALSE))

ตัวอย่าง

ต่อไปนี้เป็นตัวอย่างเล็กน้อยของฟังก์ชัน VLOOKUP:

ตัวอย่าง 1

=VLOOKUP (B3,B2:E7,2,FALSE)

VLOOKUP จะค้นหา Fontana ในคอลัมน์แรก (คอลัมน์ B) ใน table_array B2:E7 และส่งกลับ Olivier จากคอลัมน์ที่สอง (คอลัมน์ C) ของ table_array เท็จ จะส่งกลับค่าที่ตรงกันพอดี

ตัวอย่าง 2

=VLOOKUP (102,A2:C7,2,FALSE)

VLOOKUP จะค้นหาค่าที่ตรงกัน (FALSE) ของนามสกุลสําหรับ 102 (lookup_value) ในคอลัมน์ที่สอง (คอลัมน์ B) ในช่วง A2:C7 และส่งกลับ Fontana

ตัวอย่าง 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found")

IF ตรวจสอบว่า VLOOKUP ส่งกลับ Sousa เป็นนามสกุลของพนักงานที่ส่งผลเป็น 103 (lookup_value) ใน A1:E7 (table_array) หรือไม่ เนื่องจากนามสกุลที่สอดคล้องกับ 103 เป็น Leal เงื่อนไข IF จึงเป็นเท็จ และ ไม่พบ จะแสดงขึ้น

ตัวอย่าง 4

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1))

VLOOKUP จะค้นหาวันเกิดของพนักงานที่ตรงกับ 109 (lookup_value) ในช่วง A2:E7 (table_array) และส่งกลับวันที่ 03/04/1955 จากนั้น YEARFRAC จะลบวันเกิดนี้จาก 2014/6/30 และส่งกลับค่า ซึ่งจากนั้นจะถูกแปลงโดย INY เป็นจํานวนเต็ม 59

ตัวอย่าง 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"ไม่พบพนักงาน",VLOOKUP(105,A2:E7,2,FALSE))

IF ตรวจสอบเพื่อดูว่า VLOOKUP ส่งกลับค่านามสกุลจากคอลัมน์ B สําหรับ 105 (lookup_value) หรือไม่ ถ้า VLOOKUP พบนามสกุล IF จะแสดงนามสกุล มิฉะนั้น IF จะส่งกลับไม่พบพนักงาน ISNA จะตรวจสอบให้แน่ใจว่าถ้า VLOOKUP ส่งกลับ #N/A ข้อผิดพลาดนั้นจะถูกแทนที่ด้วยไม่พบพนักงาน แทนที่จะเป็น #N/A

ในตัวอย่างนี้ ค่าส่งกลับคือ Burke ซึ่งเป็นนามสกุลที่สอดคล้องกับ 105

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

เวิร์กชีตที่มีคอลัมน์ที่ใช้ VLOOKUP เพื่อรับข้อมูลจากตารางอื่น

ที่นี่ คอลัมน์ A-F และ H มีค่าหรือสูตรที่ใช้เฉพาะค่าบนเวิร์กชีต และคอลัมน์ที่เหลือใช้ VLOOKUP และค่าของคอลัมน์ A (รหัสไคลเอ็นต์) และคอลัมน์ B (Attorney) เพื่อรับข้อมูลจากตารางอื่น

คัดลอกตารางที่มีเขตข้อมูลทั่วไปลงในเวิร์กชีตใหม่ และตั้งชื่อ
คลิก ข้อมูล > เครื่องมือข้อมูล > ความสัมพันธ์ เพื่อเปิดกล่องโต้ตอบ จัดการความสัมพันธ์
สําหรับความสัมพันธ์แต่ละรายการ ให้สังเกตสิ่งต่อไปนี้:
- ฟิลด์ที่เชื่อมโยงตาราง (แสดงรายการอยู่ในวงเล็บในกล่องโต้ตอบ) นี่คือ lookup_value สําหรับสูตร VLOOKUP ของคุณ
- ชื่อตารางการค้นหาที่เกี่ยวข้อง นี่คือ table_array ในสูตร VLOOKUP ของคุณ
- เขตข้อมูล (คอลัมน์) ในตารางการค้นหาที่เกี่ยวข้องที่มีข้อมูลที่คุณต้องการในคอลัมน์ใหม่ของคุณ ข้อมูลนี้จะไม่แสดงในกล่องโต้ตอบ จัดการความสัมพันธ์ คุณจะต้องดูตารางการค้นหาที่เกี่ยวข้องเพื่อดูว่าเขตข้อมูลใดที่คุณต้องการเรียกใช้ คุณต้องการจดบันทึกหมายเลขคอลัมน์ (A=1) ซึ่งเป็น col_index_num ในสูตรของคุณ
เมื่อต้องการเพิ่มเขตข้อมูลลงในตารางใหม่ ให้ใส่สูตร VLOOKUP ของคุณในคอลัมน์ว่างแรกโดยใช้ข้อมูลที่คุณรวบรวมไว้ในขั้นตอนที่ 3

ในตัวอย่างของเรา คอลัมน์ G ใช้ Attorney (lookup_value) เพื่อรับข้อมูลอัตราบิลจากคอลัมน์ที่สี่ (col_index_num = 4) จากตารางเวิร์กชีตทนายความ tblAttorneys (table_array) ที่มีสูตร =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE)

สูตรยังอาจใช้การอ้างอิงเซลล์และการอ้างอิงช่วงได้อีกด้วย ในตัวอย่างของเรา จะเป็น =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE)
ทําการเพิ่มเขตข้อมูลต่อไปจนกว่าคุณจะมีเขตข้อมูลทั้งหมดที่คุณต้องการ ถ้าคุณกําลังพยายามเตรียมเวิร์กบุ๊กที่มีฟีเจอร์ข้อมูลที่ใช้หลายตาราง ให้เปลี่ยนแหล่งข้อมูลของฟีเจอร์ข้อมูลเป็นตารางใหม่

ปัญหา	สิ่งที่ผิดพลาด
ค่าที่ส่งกลับไม่ถูกต้อง	ถ้า *range_lookup* เป็น TRUE หรือถูกปล่อยไว้ คอลัมน์แรกจะต้องเรียงลําดับตามตัวอักษรหรือตามตัวเลข ถ้าไม่ได้เรียงลําดับคอลัมน์แรก ค่าที่ส่งกลับอาจเป็นสิ่งที่คุณไม่ได้คาดไว้ เรียงลําดับคอลัมน์แรก หรือใช้ FALSE ในการจับคู่ที่ตรงกันทุกประการ
#N/A ในเซลล์	ถ้า *range_lookup* เป็น TRUE และถ้าค่าใน *lookup_value* น้อยกว่าค่าที่น้อยที่สุดในคอลัมน์แรกของ *table_array* คุณจะได้รับค่าความผิดพลาด #N/ ถ้า *range_lookup* เป็น FALSE ค่าความผิดพลาด #N/A จะหมายความว่า ไม่พบตัวเลขที่ตรงกันทุกประการ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการแก้ไขข้อผิดพลาด #N/A ใน VLOOKUP ให้ดู วิธีแก้ไขข้อผิดพลาด #N/A ในฟังก์ชัน VLOOKUP
#REF! ในเซลล์	ถ้า *col_index_num* มากกว่าจํานวนคอลัมน์ใน *table-array* คุณจะได้รับ #REF! ค่าผิดพลาด สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการแก้ไข #REF! ใน VLOOKUP ให้ดู วิธีแก้ไขข้อผิดพลาด #REF!.
#VALUE! ในเซลล์	หาก *table_array* น้อยกว่า 1 คุณจะได้รับ #VALUE! ค่าผิดพลาด สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการแก้ไข #VALUE! ใน VLOOKUP ให้ดู วิธีแก้ไขข้อผิดพลาด #VALUE! ในฟังก์ชัน VLOOKUP.
#NAME? ในเซลล์	ข้อผิดพลาด #NAME? โดยปกติแล้วค่าจะหมายความว่าสูตรไม่มีเครื่องหมายอัญประกาศ เมื่อต้องการค้นหาชื่อของบุคคล ตรวจสอบให้แน่ใจว่าคุณใช้เครื่องหมายอัญประกาศรอบชื่อในสูตร ตัวอย่างเช่น ใส่ชื่อเป็น "Fontana" ใน =VLOOKUP("Fontana",B2:E7,2,FALSE) สําหรับข้อมูลเพิ่มเติม ให้ดู วิธีแก้ไขข้อผิดพลาด #NAME!.
ข้อผิดพลาด #SPILL! ในเซลล์	ข้อผิดพลาดนี้ #SPILL! โดยปกติแล้วจะหมายความว่าสูตรของคุณใช้จุดตัดโดยนัยสําหรับค่าการค้นหา และใช้ทั้งคอลัมน์เป็นการอ้างอิง ตัวอย่างเช่น =VLOOKUP(A:A,A:C,2,FALSE) คุณสามารถแก้ไขปัญหาได้โดยการยึดการอ้างอิงการค้นหาด้วยตัวดําเนินการ @ ดังนี้: =VLOOKUP(@A:A,A:C,2,FALSE) อีกวิธีหนึ่งคือ คุณสามารถใช้วิธี VLOOKUP แบบดั้งเดิมและอ้างอิงไปยังเซลล์เดียวแทนทั้งคอลัมน์: =VLOOKUP(A2,A:C,2,FALSE)

ให้ทำสิ่งนี้	สาเหตุ
ใช้การอ้างอิงแบบสัมบูรณ์สำหรับ range_lookup	การใช้การอ้างอิงแบบสัมบูรณ์นั้นจะช่วยคุณกรอกสูตร ดังนั้นสูตรจะค้นหาช่วงการค้นหาเดียวกันเสมอ เรียนรู้วิธีใช้การอ้างอิงเซลล์แบบสัมบูรณ์
ไม่ได้จัดเก็บตัวเลขหรือค่าวันที่เป็นข้อความ	เมื่อค้นหาค่าตัวเลขหรือค่าวันที่ ตรวจสอบให้แน่ใจว่าข้อมูลในคอลัมน์แรกของ *table_array* ไม่ได้ถูกจัดเก็บเป็นค่าข้อความ มิฉะนั้น VLOOKUP อาจส่งกลับค่าที่ไม่ถูกต้องหรือค่าที่ไม่คาดคิด
เรียงลำดับคอลัมน์แรก	เรียงลำดับคอลัมน์แรกของ *table_array* ก่อนจะใช้ VLOOKUP เมื่อ range_lookup เป็น TRUE
ใช้อักขระตัวแทน	ถ้า *range_lookup* เป็น FALSE และ *lookup_value* เป็นข้อความ คุณสามารถใช้อักขระตัวแทน เครื่องหมายคําถาม (?) และเครื่องหมายดอกจัน () ใน lookup_value* เครื่องหมายคําถามตรงกับอักขระตัวเดียวใดๆ เครื่องหมายดอกจันใช้แทนอักขระหลายตัว ถ้าคุณต้องการค้นหาเครื่องหมายคําถามหรือดอกจันจริงๆ ให้พิมพ์ เครื่องหมายตัวหนอน (~) ไว้หน้าอักขระ ตัวอย่างเช่น =VLOOKUP("Fontan?",B2:E7,2,FALSE) จะค้นหาอินสแตนซ์ทั้งหมดของ Fontana ด้วยตัวอักษรตัวสุดท้ายที่สามารถแตกต่างกันได้
ตรวจสอบให้แน่ใจว่าข้อมูลของคุณไม่มีอักขระที่ผิด	เมื่อค้นหาค่าข้อความในคอลัมน์แรก ตรวจสอบให้แน่ใจว่าข้อมูลในคอลัมน์แรกไม่มีช่องว่างอยู่ข้างหน้า ไม่มีช่องว่างอยู่ข้างหลัง ไม่มีการใช้เครื่องหมายอัญประกาศแบบตรง ( ' หรือ " ) และแบบโค้ง ( ' หรือ ") หรืออักขระที่ไม่พิมพ์ออกมา ในกรณีเหล่านี้ VLOOKUP อาจส่งกลับค่าที่ไม่คาดคิด เมื่อต้องการรับผลลัพธ์ที่ถูกต้องแม่นยำมากขึ้น ให้ลองใช้ ฟังก์ชัน CLEAN หรือ ฟังก์ชัน TRIM เพื่อเอาช่องว่างที่อยู่ข้างหลังค่าตารางในเซลล์ออก