อย่างที่เคยบอกไปแล้วว่า VLOOKUP มี 2 แบบ คือ Exact Match และ VLOOKUP Approximate Match
ซึ่งแบบ Exact Match จะ Map ข้อมูลได้ จะต้องเจอคำค้นหาอยู่ในคอลัมน์แรกของตารางอ้างอิงแบบเป๊ะๆ เท่านั้น
แต่ในหลายๆ สถานการณ์ VLOOKUP แบบ Exact Match นั้นไม่สามารถตอบโจทย์ได้ เช่น กรณีที่มีเงื่อนไขในการใช้ตารางอ้างอิงเป็นช่วง เช่น การจัดเกรด การให้ commission เป็นต้น ซึ่งจะต้องใช้ VLOOKUP Approximate Match แทน
ตัวอย่างเช่น การจัดเกรดจากคะแนนแบบนี้
จะเห็นว่า ถ้าจะทำตารางเพื่อรองรับการหาแบบ Exact Match จะต้องสร้างตารางให้มี 100 ค่าเลย เพื่อรองรับแต่ละคะแนน (จริงๆ ต้องทำ 101 ค่าด้วย เพราะต้องรองรับตั้งแต่ 0-100 คะแนน) ยิ่งที่ค่าตัวเลขคะแนนเป็นทศนิืิยมได้นี่ ไม่มีทางสร้างตารางแบบ Exact Match รองรับไว้ล่วงหน้าได้เลย
แต่ถ้าเราเปลี่ยนวิธีมาใช้ VLOOKUP แบบ Approximate Match จะแก้ปัญหานี้ได้ง่ายๆ เลย
วิธีเตรียมข้อมูลเพื่อรองรับการทำ VLOOKUP แบบ Approximate Match
วิธีเตรียมที่ง่ายที่สุด คือ ให้สร้างคอลัมน์ใหม่ไว้ทางซ้ายของข้อมูล แล้วใส่ค่าที่น้อยที่สุดในช่วงนั้นเข้าไป (ต้องเรียงจากน้อยไปมากด้วยนะ)
การใช้สูตร VLOOKUP แบบ Approximate Match
จากนั้นก็ใช้ VLOOKUP ได้ตามปกติเลย จากสูตร
=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
- table_array ให้คอลัมน์แรกสุดเรียงจากน้อยไปมาก ดังนั้นควรลากแค่ตัวข้อมูล ไม่ต้องลากหัวตาราง
- ให้เปลี่ยน Range Lookup เป็น TRUE
เช่น ใน C10 เขียนว่า =VLOOKUP(B10,A2:C6,3,TRUE) เพื่อ Lookup คะแนน 63 ซึ่งจะได้ผลเป็น C นั่นเอง
การทำงานที่แท้จริงของ VLOOKUP Approximate Match
การใช้งาน VLOOKUP Approximate Match ที่ผมอธิบายไปด้านบนนั้น เป็นแบบที่ทำให้ใช้งานจริงได้ง่าย แต่ในความเป็นจริงแล้ว VLOOKUP แบบนี้มีการทำงานที่ซับซ้อนกว่านั้น คือ มันจะใช้การค้นหาข้อมูลแบบ Binary Search ซึ่งมีความรวดเร็วมาก แต่จะมีข้อจำกัดคือ ต้องเรียงค่าที่เราจะค้นหาในตารางอ้างอิงจากน้อยไปมากเสียก่อน นั่นคือ ข้อมูลในคอลัมน์ซ้ายสุดของตารางอ้างอิงจะต้องเรียงจากน้อยไปมากเท่านั้น (ไม่จำเป็นต้องเป็นตัวเลข จะเป็นตัวหนังสือก็ได้) ไม่งั้นผลการ VLOOKUP อาจจะ Error
และหลังจาก หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) โดยทำการหาจากบนลงล่าง (Vertical Lookup) จนเจอตัวที่มากกว่าค่าที่กำหนดแล้วจะเด้งกลับขึ้นข้างบน 1 บรรทัด
ผลลัพธ์จากการใช้ฟังก์ชัน
- ถ้าเจอค่าที่ต้องการเป๊ะๆ : ถ้ามีค่าเดียว มันจะยึดค่าบรรทัดนั้น ถ้ามี หลายค่า มันจะยึดที่เจออันล่างสุด(เจอค่าที่มากกว่าแล้วเด้งขึ้น 1 บรรทัด)
- ถ้าไม่เจอค่าที่ต้องการเป๊ะๆ :ขึ้นอยู่กับค่าคำค้นหาเทียบกับค่าในตารางอ้างอิง
- อยู่ระหว่างค่าในตาราง : จะหาตัวที่มากกว่าคำค้นหาแล้วเด้งกลับ 1 ช่อง
- มากกว่าทุกค่าในตาราง : เจอตัวล่างสุด เพราะเด้งมา 1 ช่องจากนอกตาราง
- น้อยกว่าทุกค่าในตาราง : จะแสดงเป็น #N/A (เป็นกรณีเดียวที่จะ Error)
และถ้าข้อมูลเรียงมั่วจะเกิดอะไรขึ้น?
ผมได้ทำคลิปเดาการทำงานของ VLOOKUP Approximate Match แบบเป๊ะๆ เอาไว้ ใครสนใจลองไล่ดูได้ครับ