คู่มือ VLOOKUP จำเป็นสำหรับ Excel และ Google Sheets

ใช้ Excel หรือ Google Sheets นานพอ คุณจะเจอคำว่า VLOOKUP แน่นอน มันคืออะไร และทำอะไรได้บ้าง? ในคู่มือนี้ เราพูดถึง VLOOKUP ทำอะไร วิธีใช้ทำให้งานง่ายขึ้น และคำแนะนำการใช้งานที่ดีที่สุดใน Excel และ Google Sheets

เรายังตอบคำถามรอบๆ VLOOKUP และ common pitfalls ที่อาจเจอเมื่อพยายามใช้คำสั่งนี้

VLOOKUP คืออะไร?

VLOOKUP – ย่อมาจาก "vertical lookup" – เป็นฟังก์ชันที่มาจาก Microsoft Excel ให้คุณค้นหาค่าเฉพาะในคอลัมน์ แล้วใช้ข้อมูลนั้นดึงค่าอื่นในแถวเดียวกันมาแสดง

ตัวอย่าง: คุณมี 3 คอลัมน์ชื่อ "name", "number", "address" เต็มไปด้วยข้อมูล ใช้ VLOOKUP ค้นหาชื่อเฉพาะในคอลัมน์ "name" แล้วแสดง number หรือ address ที่อยู่แถวเดียวกับชื่อนั้น จำไว้: VLOOKUP ไม่ case sensitive

แม้ดูไม่ค่อย useful ในข้อมูลเล็กน้อยในตัวอย่าง แต่มา handy มากเมื่อมีข้อมูลเยอะ และอยากใช้ค่าบางค่าในพื้นที่อื่น

คุณสร้าง master list ข้อมูลในชีตเดียว แล้วใช้ VLOOKUP ในชีตถัดไปดึงข้อมูลจาก master list วิธีนี้ ต้องอัปเดตแค่ชีตเดียว ค่าจะอัปเดตอัตโนมัติข้ามชีตอื่นๆ

ง่ายๆ สตริง VLOOKUP เป็นดังนี้:

=VLOOKUP( ค่าที่ต้องการค้นหา, ช่วงเซลล์ที่ค้นหา, หมายเลขคอลัมน์ของค่าที่ต้องการแสดง, exact หรือ approximate match )

วิธีใช้ VLOOKUP ใน Excel และ Google Sheets

ดูครั้งแรก สตริง VLOOKUP อาจดูน่ากลัว แต่จริงๆ เรียบง่ายกว่าที่คิด ตามคำแนะนำลึกๆ นี้

1. ต้องการตารางข้อมูลเพื่อดึงมา ใช้ idea เดียวกับตัวอย่างก่อนหน้า เพิ่มข้อมูลใต้ 3 คอลัมน์: Name, Address, Phone Number

2. เราต้องการดึง phone number ที่ตรงกับชื่อในฟิลด์เฉพาะ กรณีนี้ ต้องการ phone number ของ "Iris Watson"

3. เริ่มสตริง โดย double-click เซลล์ว่าง พิมพ์ =VLOOKUP( จะเห็นค่าแรกที่ต้องการคือ "lookup_value" ซึ่งคือข้อมูลใช้ค้นหา phone number

4. เนื่องจากเราเพิ่มชื่อ "Iris Watson" ในเซลล์ก่อนหน้า VLOOKUP แล้ว เซลล์นั้นคือ lookup_value สตริงตอนนี้ควรเป็น: =VLOOKUP(e12,

5. ต่อมา table_array คือตารางข้อมูลทั้งหมดที่ดึงข้อมูลมา Highlight ตารางทั้งหมดที่ข้อมูลอยู่ ตอนนี้สตริงควรคล้าย: =VLOOKUP(e12,A1:C5,

6. ค่าที่ 3 ในสตริงคือ "col_index_number" หรือ column index number หมายเลขคอลัมน์ที่พยายามดึงข้อมูล ใน table array ที่เลือก "Name" คือ 1, "Address" คือ 2, "Phone Number" คือ 3 เรา VLOOKUP phone number สตริงเป็น: =VLOOKUP(e12,A1:C5,3,

7. ส่วนสุดท้าย: exact match หรือ approximate match พิมพ์ TRUE สำหรับ approximate FALSE สำหรับ exact เราเลือก exact สตริงสุดท้าย: =VLOOKUP(E12,A1:C5,3,FALSE)

8. เห็นได้ว่า VLOOKUP ดึง phone number ของ "Iris Watson" สำเร็จ

หมายเหตุ: คู่มือนี้ใช้ Microsoft Excel ทำ VLOOKUP แต่ใช้วิธีเดียวกันกับ Google Sheets ได้

วิธีกรองข้อมูลด้วยหลายเกณฑ์

VLOOKUP ออกแบบมาเพื่อดึงค่าเดียว มีฟังก์ชันอื่นสำหรับ lookup หลายแหล่ง – ไม่ได้หมายถึงไม่สามารถใช้ VLOOKUP ค้นหาหลายเกณฑ์ โดยใช้ helper column สร้าง unique identifiers เก็บข้อมูลจากหลายเซลล์ แล้วปรับ VLOOKUP ให้ค้นหา unique identifiers แทน

มีประโยชน์เมื่อมีหลายเซลล์ค่าเดียวกัน สมมติตารางมีหลายเซลล์ชื่อ "Iris Watson" ปกติ VLOOKUP ดึงแค่ "Iris Watson" ตัวแรกที่เจอ แต่อาจต้องการอีกตัว helper column สร้าง unique identifier ช่วย VLOOKUP แยก "Iris Watson" ต่างๆ ได้

ตัวอย่างนี้ ใช้ VLOOKUP แสดง address แทน phone number เพิ่ม "Iris Watson" คนที่สอง address และ phone number ต่างกัน

1. สร้าง helper column รวม "Name" และ "Phone Number" สร้าง unique identifier ใช้ CONCATENATE function รวม strings จากเซลล์ต่างๆ สตริงควรเป็น: =CONCATENATE(B2," | ",D2) ใส่ pipe "|" และ space ระหว่าง name และ phone number ให้เข้าใจง่าย หากอยากรู้เพิ่มเติมฟังก์ชันนี้ อ่านบทความ how to use CONCATENATE

2. concatenate สำเร็จ คลิกมุมขวาล่างเซลล์ที่ highlight ลากลงเซลล์อื่นใน helper column จะ apply concatenation เดียวกัน

3. ต้องการ 2 fields สำหรับ VLOOKUP เพิ่ม "Name" และ "Number" lookup fields รวม "Address" field แสดงผล VLOOKUP

4. เราต้องการ VLOOKUP รวมข้อมูลจาก "Name" และ "Number" search fields ให้เข้ากับ format เดียวกับ helper column วิธีนี้ VLOOKUP จะเจอ unique identifier ใน helper column และดึง address ที่ตรงกัน

สตริงตอนแรก: =VLOOKUP(F9&" | "&F10, F9 และ F10 คือ "Name" และ "Address" search fields ตามลำดับ & symbol ทำงานคล้าย concatenate รวมทั้งสองฟิลด์ ส่วน " | " คือ divider เดียวกับ concatenation ใน helper column

5. ต่อไปทำตาม VLOOKUP formula ที่เหลือ select table array ใส่ column index number ตั้ง EXACT match สตริงสุดท้าย: =VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)

6. เห็นได้ว่าใส่แค่ name หรือ address ใน search fields ไม่พอ ทั้งคู่ต้องกรอกเพื่อ successful lookup ดูด้านล่าง 2 ผลลัพธ์ VLOOKUP ต่างกัน เพราะมี "Iris Watson" 2 คน ผลลัพธ์ต่างกันไปตาม phone number ที่ใส่ใน search field ที่ 2

VLOOKUP ดีกว่า INDEX-MATCH ไหม?

คำถามนี้ถاجกันมาตั้งแต่ยุคแรกของ Microsoft Excel ก่อนตอบ สำคัญคือเข้าใจ INDEX-MATCH คืออะไร INDEX และ MATCH เป็น 2 ฟังก์ชันแยกกัน ที่มักรวมกันสร้าง lookup system versatile กว่า VLOOKUP

MATCH function ใช้เมื่ออยากหา position number ของค่าเฉพาะในช่วงเซลล์ INDEX ใช้ 2 formats แสดงค่าจาก table array หรือช่วงเซลล์ รวมทั้งคู่ ใช้ MATCH หา position numbers ของข้อมูลที่ให้มา แล้ว INDEX ใช้ position numbers คืนค่า

อันไหนดีกว่า ขึ้นอยู่กับผู้ใช้ VLOOKUP friendly กับ newbies และ mid-tier users มากกว่า เพราะ setup และใช้งานง่ายกว่า แต่ INDEX-MATCH flexible มากกว่า ใช้ได้ในสถานการณ์กว้างขึ้น

วันสิ้นสุด หลายคนรู้ VLOOKUP มากกว่า INDEX-MATCH ดังนั้น VLOOKUP น่าจะดีกว่าถ้าชีตจะถูกเข้าถึงโดย non-advanced users หลายคน แต่ถ้าชีตสำหรับ experts INDEX-MATCH น่าจะเป็นทางเลือก

Common Dos และ Don'ts เมื่อใช้ VLOOKUP

ไม่เสียหายที่จะผิดพลาดนิดหน่อยตอนใหม่กับ VLOOKUP Excel veterans ส่วนใหญ่เคยผ่านมาแล้ว นี่คือสิ่งควรจำ

1. DO ตรวจสอบว่า Lookup_Value อยู่คอลัมน์แรกใน table

VLOOKUP ทำงาน assuming lookup_value อยู่คอลัมน์แรกใน table array มันดึงข้อมูลได้ก็ต่อเมื่อค่านั้นอยู่ในเซลล์ถัดไปจากแถวเดียวกัน วาง lookup_value ที่อื่นนอกจากต้นทาง ฟังก์ชันจะ fail

2. DON'T ลืมใช้ FALSE สำหรับ exact matches

ส่วนสุดท้ายของ VLOOKUP string ให้คุณ dictate ว่า FALSE สำหรับ exact match หรือ TRUE สำหรับ partial match หลาย user ผิดพลาดใช้ TRUE ซึ่งอาจนำไปสู่ inaccurate results หรือลืมตั้งค่าเลย

3. DO ตรวจสอบ column index number ซ้ำ

สิ่งที่ VLOOKUP แสดง ขึ้นอยู่กับการตั้ง "col_index_num" ให้ถูกต้อง "col_index_num" คือ numerical value สำหรับแต่ละคอลัมน์ใน table array คอลัมน์แรก = 1, ที่สอง = 2 ต่อไป ถ้าใส่ "col_index_num" ผิด ฟังก์ชันจะแสดงผลต่างไปหมด

4. DO ใช้ F4 เมื่อ copy สูตรไปเซลล์อื่น

ประโยชน์ของ VLOOKUP คือ drag formula ลงคัดลอกข้ามเซลล่าต่างๆ ปัญหา: ค่าใน function string จะ shift ลงไปด้วย ทำลายสูตรทั้งหมด ป้องกันได้: วาง cursor บนค่าในสูตร กด F4 จะกลายเป็น absolute values ที่ไม่ shift เมื่อ copy formula

Usual VLOOKUP Errors และวิธีแก้

Error พบบ่อยสุดคือ "#NA" แม้มีหลายเหตุผลทำให้ error นี้ขึ้น

1. Lookup_value ไม่อยู่คอลัมน์แรกของ table array

ข้อจำกัดใหญ่ของ VLOOKUP: ค้นหาค่าได้แค่คอลัมน์แรกของ table array หาก lookup_value ไม่อยู่ที่นั่น จะได้ #NA error แก้ได้: ปรับ formula 參照 คอลัมน์อื่น หรือย้ายคอลัมน์ ให้ lookup_value อยู่ที่ถูกต้อง

2. VLOOKUP หา exact match ไม่เจอ

ค่าสุดท้ายใน VLOOKUP formula คือ range_lookup argument ตั้ง TRUE สำหรับ approximate match หรือ FALSE สำหรับ exact หากตั้ง FALSE และ VLOOKUP หา exact match ไม่เจอ จะได้ #NA error

หากมั่นใจว่าควรมี match ใน lookup_value ต้อง double-check ข้อมูลใน table array ให้แน่ใจว่า formatted ถูกต้อง ไม่มี unwanted spaces Non-printing characters ก็อาจป้องกัน VLOOKUP หา items ไม่ได้

3. Floating point numbers ใหญ่เกินไป

โดยพื้นฐาน floating point numbers คือตัวเลขที่มีทศนิยม กับ VLOOKUP หากมี figure ที่มีตัวเลขหลังทศนิยมมากเกินไป จะได้ #NA error แก้ง่าย: ปัดเศษตัวเลขสูงสุด 5 ตำแหน่งทศนิยม ก็ควรทำงาน ใช้ฟังก์ชัน ROUND ได้

คำถามที่พบบ่อย

1. ถ้าทิ้ง range_lookup argument ว่าง จะเกิดอะไร?

Argument ที่ 4 ใน VLOOKUP string – ควรตั้ง "TRUE" หรือ "FALSE" – ถือว่า optional ตั้ง "TRUE" = VLOOKUP หา approximate match "FALSE" = ต้อง exact ถ้าทิ้ง argument นี้ว่าง VLOOKUP default เป็น "TRUE" อาจทำให้ผลลัพธ์ไม่ตรงที่ต้องการ

2. ทางเลือกแทน VLOOKUP มีอะไร?

Best alternative: INDEX-MATCH combo จำไว้ว่า combination นี้ยากกว่า เพราะต้อง master 2 ฟังก์ชันต่างกัน และ ensure ทำงานร่วมกันได้

3. ค้นหาค่าโดย rows แทน columns ได้ไหม?

ได้ ใช้ HLOOKUP หรือ "horizontal lookup" ใน Excel และ Google Sheets ฟังก์ชันนี้ lookup ค่าจาก row เฉพาะ แล้วแสดงค่าจาก row อื่นแต่ column เดียวกัน

หากต้องการจำกัดการค้นหาใน row หรือ column เดียว ใช้ LOOKUP function