VLOOKUP() ใน Google Sheets: วิธีใช้พร้อมตัวอย่าง

VLOOKUP() คือฟังก์ชันของ Google Sheets ที่ค้นหาค่าหนึ่งในคอลัมน์ และคืนค่าที่ตรงกันจากอีกคอลัมน์ในแถวเดียวกัน เพียงระบุสิ่งที่ต้องการหา บอกว่าจะให้มองหาที่ไหน แล้วฟังก์ชันจะดึงข้อมูลที่ต้องการกลับมา

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

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

รูปแบบไวยากรณ์ของ VLOOKUP() ใน Google Sheets คืออะไร?

รูปแบบไวยากรณ์ของ VLOOKUP() คือ:

=VLOOKUP(search_key, range, index, [is_sorted])

โดย:

• search_key คือค่าที่ต้องการหา

• range คือช่วงตารางที่ใช้ค้นหา

• index คือหมายเลขคอลัมน์ที่ต้องการคืนค่า

• [is_sorted] ระบุว่าต้องการผลแบบตรงตัวหรือไม่

จะทำ VLOOKUP() ใน Google Sheets ได้อย่างไร?

ขั้นตอนการใช้ VLOOKUP() ใน Google Sheets:

1. ตัดสินใจว่าต้องการค้นหาค่าใด: อาจเป็น ID ชื่อ หรือค่าที่มีเอกลักษณ์ใดๆ

2. กำหนดช่วงตาราง: ให้แน่ใจว่าคอลัมน์ที่มีค่าที่จะค้นหาอยู่เป็นคอลัมน์แรกของช่วงนั้น

3. เลือกข้อมูลที่ต้องการให้คืนค่า: นั่นคือคอลัมน์ของผลลัพธ์ (เช่น ชื่อ ราคา สถานะ)

4. ใส่สูตร VLOOKUP() ในเซลล์ใหม่: อ้างอิงค่าที่ต้องการค้นหา ช่วงตาราง และคอลัมน์ที่ต้องการคืนค่า

5. ใช้การจับคู่แบบตรงตัวหรือใกล้เคียง (FALSE หรือ TRUE): โดยทั่วไปควรใช้แบบตรงตัวเพื่อหลีกเลี่ยงผลลัพธ์ที่ไม่ถูกต้อง (รายละเอียดภายหลัง)

หากมีตารางนี้:

และต้องการหาชื่อของ ID 102 ให้ใช้:

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

สูตรนี้บอกให้ Google Sheets ทำดังนี้:

• ค้นหา 102 ในคอลัมน์ A
• คืนค่าจากคอลัมน์ B
• ใช้การจับคู่แบบตรงตัว

ผลลัพธ์ที่ได้คือ Sarah

ตัวอย่าง VLOOKUP() ภาพโดยผู้เขียน

VLOOKUP() ทำงานอย่างไร (ลำดับตรรกะแบบทีละขั้น)

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

เมื่อพบค่าตรงกันแล้ว จะอยู่ในแถวนั้นและเลื่อนไปยังคอลัมน์ที่ระบุ แล้วคืนค่านั้น

ขั้นตอนมีดังนี้:

• เริ่มที่คอลัมน์แรกของช่วง
• ค้นหาลงมาตามคอลัมน์
• พบค่าที่ตรงกันตัวแรก
• เลื่อนไปในแถวเดียวกัน
• คืนค่าจากคอลัมน์ที่กำหนด

สมมติว่าต้องการหาตำแหน่งงานของพนักงานรหัส E1007 ให้ใช้สูตรต่อไปนี้:

=VLOOKUP("E1007", A:G, 5, FALSE)

VLOOKUP() ใน Google Sheets ภาพโดยผู้เขียน

สูตรนี้:

• ค้นหา E1007 ในคอลัมน์ A

• เลื่อนไปในแถวเดียวกัน

• คืนค่าคอลัมน์ที่ 5 (Job Title)

• ให้ผลลัพธ์เป็น Software Engineer

หมายเหตุ: VLOOKUP() ค้นหาได้จากซ้ายไปขวาเท่านั้น หากคอลัมน์ที่ใช้ค้นหาไม่ใช่คอลัมน์แรกของช่วง ฟังก์ชันจะทำงานไม่ได้ ในกรณีนี้ต้องจัดเรียงข้อมูลใหม่หรือใช้ฟังก์ชันอื่น เดี๋ยวจะกล่าวถึงภายหลัง

Exact Match กับ Approximate Match ใน VLOOKUP()

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

ใน VLOOKUP() จะควบคุมด้วยอาร์กิวเมนต์สุดท้าย ค่า FALSE หรือ TRUE มักถูกเข้าใจผิด เพราะ TRUE ดูไม่เป็นอันตรายแต่จริงๆ ไม่ใช่

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

แนะนำให้ใช้ TRUE เฉพาะกรณี lookup แบบขั้นบันได เช่น ภาษีหรือช่วงเกรด ที่การประมาณเป็นพฤติกรรมที่ถูกต้องตามการออกแบบ สำหรับกรณีอื่นให้ใช้ FALSE

การจับคู่แบบตรงตัว (FALSE)

ค่านี้บอก VLOOKUP() ว่า “คืนค่าเฉพาะเมื่อพบค่านี้แบบตรงตัวเท่านั้น” ตัวอย่างเช่น สูตร =VLOOKUP("E1005", A:G, 7, FALSE) จะค้นหา E1005 คืนเงินเดือนจากคอลัมน์ที่ 7 และให้ผลลัพธ์เป็น 90000

หากไม่มีค่านั้น จะได้ข้อผิดพลาด #N/A

การจับคู่แบบตรงตัวใน VLOOKUP() ภาพโดยผู้เขียน

การจับคู่แบบใกล้เคียง (TRUE)

ค่านี้บอก VLOOKUP() ว่า “ถ้าไม่พบค่าตรงตัว ให้คืนค่าที่ต่ำกว่าและใกล้ที่สุด” ฟังดูช่วยได้—บางทีก็ใช่ แต่บางครั้งก็ดึงคำตอบผิดแบบเงียบๆ

เช่น ในชุดข้อมูลของเราไม่มี E1011 รหัสพนักงานสุดท้ายคือ E1010 ดังนั้นถ้าใช้สูตร =VLOOKUP("E1011", A:G, 7, TRUE) VLOOKUP() จะไม่แจ้งข้อผิดพลาด แต่จะคืนค่าที่ตรงกับตัวที่ต่ำกว่าและใกล้ที่สุดคือ E1010 เซลล์จะแสดง 88000

แม้จะไม่มี E1011 แต่ VLOOKUP() ก็ยังให้ผลลัพธ์เพราะ TRUE อนุญาตให้จับคู่แบบใกล้เคียง

การจับคู่แบบใกล้เคียงใน VLOOKUP() ภาพโดยผู้เขียน

ลองนึกภาพข้อมูลชุดเดียวกันแต่เรียงลำดับไม่ถูกต้อง แล้วใส่รหัสพนักงานที่มีอยู่ในสูตรแบบนี้: =VLOOKUP("E1011", A:G, 7, TRUE).

ครั้งนี้สูตรจะคืนเงินเดือนผิด เพราะคอลัมน์สำหรับค้นหาไม่ถูกจัดเรียง และการจับคู่แบบใกล้เคียงต้องอาศัยข้อมูลที่เรียงแล้ว

VLOOKUP() แบบใกล้เคียงทำให้เกิดข้อผิดพลาดเพราะรายการไม่เรียง ภาพโดยผู้เขียน

ข้อผิดพลาดที่พบบ่อยใน VLOOKUP() และวิธีแก้

มาดูปัญหาที่พบบ่อยกับ VLOOKUP() และวิธีแก้ไขกัน:

ข้อผิดพลาด #N/A

ข้อผิดพลาด #N/A หมายความว่า VLOOKUP() หาค่าที่ระบุไม่พบ

สมมติว่ามีสูตรนี้: =VLOOKUP("E9999", A:G, 7, FALSE)

หากไม่มี E9999 อยู่ในคอลัมน์แรกของช่วง สูตรจะคืนค่า #N/A

กรณีนี้ยังเกิดขึ้นได้หากค่าที่พิมพ์ในชีตต่างไป เช่น มีช่องว่างเกิน หรือมีปัญหาการจัดรูปแบบข้อความ

วิธีแก้:

• ตรวจสอบว่าค่ามีอยู่จริงในคอลัมน์แรกของช่วง

• ใช้การอ้างอิงเซลล์แทนการพิมพ์ค่าด้วยตนเองเมื่อเป็นไปได้

• ใส่เครื่องหมายอัญประกาศเมื่อระบุค่าข้อความแบบฮาร์ดโค้ด

• ลบช่องว่างต้นหรือท้ายข้อมูล

• ใช้ FALSE เฉพาะเมื่อจำเป็นต้องได้ค่าตรงตัว

ระบุหมายเลขคอลัมน์ผิด

หมายเลขคอลัมน์บอก VLOOKUP() ว่าจะคืนค่าจากคอลัมน์ใดภายในช่วงที่เลือก หากหมายเลขมากเกินไป สูตรจะคืนค่า #REF!

สูตร =VLOOKUP("E1005", A:G, 8, FALSE) จะได้ #REF! เพราะช่วง A:G มีเพียง 7 คอลัมน์

ระบุหมายเลขคอลัมน์ผิดทำให้เกิดข้อผิดพลาด ภาพโดยผู้เขียน

หรืออาจได้ผลลัพธ์ผิดได้เช่นกันหากหมายเลขคอลัมน์ถูกต้องแต่ชี้ไปยังคอลัมน์ที่ไม่ใช่ค่าที่ต้องการ

เช่น:

=VLOOKUP("E1005", A:G, 5, FALSE)

สูตรนี้จะคืนตำแหน่งงาน ไม่ใช่เงินเดือน

วิธีแก้:

• นับคอลัมน์จากคอลัมน์แรกของช่วงที่เลือก
• ตรวจสอบว่าหมายเลขคอลัมน์ตรงกับค่าที่ต้องการคืน
• ตรวจสอบให้แน่ใจว่าคอลัมน์นั้นอยู่ในช่วง

ปัญหาการจับคู่แบบใกล้เคียง

หากใช้ TRUE VLOOKUP() จะมองหาค่าที่ต่ำกว่าและใกล้ที่สุดแทนที่จะต้องการค่าตรงตัว

ถ้าพิมพ์ =VLOOKUP("E1011", A:G, 7, TRUE) แล้วไม่มี E1011 ในชุดข้อมูล สูตรจะไม่แจ้งข้อผิดพลาด แต่จะคืนค่า 88000 ซึ่งเป็นของ E1010

ผลลัพธ์นั้นอาจดูถูกต้อง แต่ไม่ใช่การจับคู่แบบตรงตัว

วิธีแก้:

• ใช้ FALSE เมื่อจำเป็นต้องได้ผลลัพธ์ตรงตัว

• ใช้ TRUE เฉพาะเมื่อข้อมูลเรียงลำดับแล้วและยอมรับผลแบบใกล้เคียงได้

ปัญหาเกี่ยวกับช่วง (range)

VLOOKUP() อาจใช้งานไม่ได้เมื่อช่วงที่เลือกไม่สอดคล้องกับสิ่งที่สูตรพยายามทำ

เช่น =VLOOKUP("E1005", A:C, 7, FALSE) จะได้ #REF! เพราะช่วง A:C มีเพียง 3 คอลัมน์ แต่สูตรขอคอลัมน์ที่ 7

อีกปัญหาที่พบบ่อยคือคอลัมน์สำหรับค้นหาไม่ได้อยู่เป็นคอลัมน์แรกของช่วง

ดูสูตรนี้:

=VLOOKUP("E1005", B:G, 7, FALSE)

ที่นี่คอลัมน์แรกของช่วงคือคอลัมน์ B ไม่ใช่คอลัมน์ A และเนื่องจาก VLOOKUP() ค้นหาเฉพาะคอลัมน์แรกของช่วงที่เลือก จึงหา E1005 ไม่พบ

วิธีแก้:

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

ช่วงผิดทำให้เกิดข้อผิดพลาด ภาพโดยผู้เขียน

VLOOKUP() ข้ามชีตใน Google Sheets

สามารถใช้ VLOOKUP() เพื่อดึงข้อมูลจากชีตอื่นได้ โดยเพิ่มชื่อชีตก่อนช่วง

รูปแบบเป็นดังนี้:

=VLOOKUP(search_key, SheetName!range, index, FALSE)

ส่วน SheetName! จะบอกสูตรให้ค้นหาในชีตใด

สมมติว่าข้อมูลอยู่ในชีตชื่อ Employees และกำลังทำงานอยู่ในชีตอื่น ต้องการหาฝ่าย (department) ของพนักงานรหัส E1005 ให้ใช้:

=VLOOKUP("E1005", Employees!A:G, 4, FALSE)

โดย:

• Employees!A:G คือให้ค้นหาในชีต Employees

• 4 คือคืนค่าคอลัมน์ Department

สูตรจะคืนค่าฝ่ายของ E1005

อ้างอิงชีตอื่นใน VLOOKUP() ภาพโดยผู้เขียน

เมื่อชื่อชีตมีช่องว่าง

ถ้าชื่อชีตมีช่องว่าง ให้ครอบด้วยอัญประกาศเดี่ยวดังนี้:

=VLOOKUP("E1005", 'Employee Data'!A:G, 7, FALSE)

หากไม่มีอัญประกาศ สูตรจะใช้งานไม่ได้

VLOOKUP() เทียบกับ INDEX MATCH ใน Google Sheets

VLOOKUP() ค้นหาในคอลัมน์ที่ตรึงไว้และคืนค่าตามตำแหน่ง ส่วน INDEX MATCH ค้นหาค่าด้วยการอ้างอิงแถวและคอลัมน์โดยตรง จึงยังทำงานได้แม้โครงสร้างตารางเปลี่ยน

VLOOKUP: เขียนเร็ว แต่พังง่าย

ตัวอย่างที่เห็นไปแล้ว =VLOOKUP("E1005", A:G, 7, FALSE) ใช้หาค่าเงินเดือนของ E1005

ใช้ได้ดีเมื่อ:

• คอลัมน์สำหรับค้นหาอยู่ด้านซ้าย
• โครงสร้างตารางไม่เปลี่ยน

ปัญหาเริ่มขึ้นเมื่อโครงสร้างเปลี่ยน

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

• มีการแทรกคอลัมน์ใหม่
• คอลัมน์เงินเดือนเลื่อนจาก 7 เป็น 8
• แต่สูตรยังคงใช้เลข 7

ผลลัพธ์จึงผิดโดยไม่แจ้งข้อผิดพลาด

INDEX MATCH: ยาวกว่า แต่เสถียร

มาดูการค้นหาแบบเดียวกันด้วย INDEX MATCH:

=INDEX(G:G, MATCH("E1005", A:A, 0))

โดย:

• MATCH() หาอันดับตำแหน่งของ E1005 ในคอลัมน์ A

• INDEX() คืนค่าจากคอลัมน์ G ที่ตำแหน่งนั้น

วิธีนี้ไม่ขึ้นอยู่กับหมายเลขคอลัมน์ จึงยังคงทำงานได้แม้ตารางเปลี่ยน

INDEX MATCH จัดการข้อมูลได้ดีกว่า VLOOKUP() ภาพโดยผู้เขียน

ใช้เมื่อใดจึงจะช่วยได้จริง

ใช้ INDEX MATCH เมื่อข้อมูลไม่ได้จัดจากซ้ายไปขวา สมมติว่าคอลัมน์ A เก็บรหัสพนักงาน และคอลัมน์ C เก็บนามสกุล หากต้องการค้นหาด้วยนามสกุลแล้วคืนค่ารหัสพนักงาน นี่คือการค้นหาจากขวาไปซ้าย

VLOOKUP() ทำไม่ได้หากไม่เปลี่ยนตาราง แต่ INDEX MATCH จัดการได้โดยตรง:

=INDEX(A:A, MATCH("Wilson", C:C, 0))

ไม่ต้องจัดเรียงข้อมูลใหม่

VLOOKUP() เทียบกับ XLOOKUP ใน Google Sheets

VLOOKUP() ค้นหาภายในโครงสร้างที่ตายตัวและมีข้อจำกัด ขณะที่ XLOOKUP() ให้ค้นหาได้ทุกทิศทาง ฟังก์ชันนี้ถูกเพิ่มใน Google Sheets ในปี 2022 และตอนนี้มักเป็นตัวเลือกที่ดีกว่าในหลายกรณี

สมมติว่าต้องการห้ารหัสพนักงานจากนามสกุล Wilson นี่คือการค้นหาจากขวาไปซ้าย VLOOKUP() ทำไม่ได้หากไม่จัดเรียงตารางใหม่ แต่ XLOOKUP() ทำได้:

=XLOOKUP("Wilson", C:C, A:A)

สูตรนี้:

• ค้นหาในคอลัมน์นามสกุล (คอลัมน์ C)
• คืนค่ารหัสพนักงาน (คอลัมน์ A)

XLOOKUP() ใน Google Sheets ภาพโดยผู้เขียน

XLOOKUP() ดีกว่าเพราะ:

• ทำงานได้ทุกทิศทาง (ซ้ายหรือขวา)
• ไม่ใช้หมายเลขคอลัมน์
• ค่าเริ่มต้นเป็นการจับคู่แบบตรงตัว
• กำหนดผลลัพธ์เองได้เมื่อไม่พบค่าที่ตรงกัน

สรุป

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

เมื่อคุ้นเคยแล้ว ลองเปลี่ยนประเภทการจับคู่ ปรับช่วง และสังเกตว่าเมื่อใดผลลัพธ์เริ่มไม่สมเหตุสมผล นั่นมักเป็นจุดที่ตัวเลือกที่ยืดหยุ่นกว่าอย่าง INDEX MATCH() หรือ XLOOKUP() เหมาะสมกว่า