สูตร Vlookup จะทำงานก็ต่อเมื่ออาร์เรย์ของตารางในสูตรไม่เปลี่ยนแปลง แต่ถ้ามีคอลัมน์ใหม่แทรกลงในตารางหรือคอลัมน์ถูกลบออกสูตรจะให้ผลลัพธ์ที่ไม่ถูกต้องหรือสะท้อนถึงข้อผิดพลาดเพื่อให้สูตรปราศจากข้อผิดพลาดใน สถานการณ์แบบไดนามิกดังกล่าวเราใช้ฟังก์ชันการจับคู่เพื่อจับคู่ดัชนีของข้อมูลและส่งคืนผลลัพธ์จริง
รวม VLOOKUP กับ Match
สูตร vlookup เป็นฟังก์ชันที่ใช้บ่อยที่สุดซึ่งใช้เพื่อค้นหาและส่งคืนค่าเดียวกันในดัชนีคอลัมน์ที่ระบุหรือค่าจากดัชนีคอลัมน์อื่นโดยอ้างอิงถึงค่าที่ตรงกันจากคอลัมน์แรก ความท้าทายที่สำคัญที่ต้องเผชิญขณะใช้ vlookup คือดัชนีคอลัมน์ที่จะระบุเป็นแบบคงที่และไม่มีฟังก์ชันการทำงานแบบไดนามิก โดยเฉพาะอย่างยิ่งเมื่อคุณกำลังทำงานกับหลายเกณฑ์ที่ต้องการให้คุณเปลี่ยนดัชนีคอลัมน์อ้างอิงด้วยตนเอง ดังนั้นความต้องการนี้จึงถูกเติมเต็มโดยใช้สูตร“ MATCH” เพื่อให้มีการยึดเกาะหรือควบคุมดัชนีคอลัมน์ที่เปลี่ยนแปลงบ่อยในสูตร VLOOKUP ได้ดีขึ้น
VLookup และ Match Formula
# 1 - สูตร VLOOKUP
สูตรของฟังก์ชัน VLOOKUP ใน Excel
ข้อโต้แย้งทั้งหมดที่ต้องป้อนมีผลบังคับที่นี่
- Lookup_value - ที่นี่ควรป้อนเซลล์หรือข้อความอ้างอิงที่มีเครื่องหมายคำพูดคู่เพื่อระบุในช่วงคอลัมน์
- อาร์เรย์ตาราง- อาร์กิวเมนต์นี้ต้องการให้ป้อนช่วงของตารางซึ่งควรค้นหา Lookup_value และข้อมูลที่จะเรียกคืนอยู่ในช่วงคอลัมน์เฉพาะ
- Col_index_num -ในอาร์กิวเมนต์นี้จำเป็นต้องป้อนหมายเลขดัชนีคอลัมน์หรือจำนวนคอลัมน์จากคอลัมน์แรกของการอ้างอิงซึ่งต้องดึงค่าที่เกี่ยวข้องจากตำแหน่งเดียวกับค่าที่ค้นหาในคอลัมน์แรก
- [Range_lookup] -อาร์กิวเมนต์นี้จะให้สองตัวเลือก
- TRUE - การจับคู่โดยประมาณ: - อาร์กิวเมนต์สามารถป้อนเป็น TRUE หรือตัวเลข“ 1” ซึ่งจะส่งคืนการจับคู่โดยประมาณที่สอดคล้องกับคอลัมน์อ้างอิงหรือคอลัมน์แรก ยิ่งไปกว่านั้นค่าในคอลัมน์แรกของอาร์เรย์ตารางจะต้องเรียงลำดับจากน้อยไปมาก
- FALSE - การจับคู่แบบตรงทั้งหมด: - ที่นี่อาร์กิวเมนต์ที่จะป้อนอาจเป็น FALSE หรือตัวเลข“ 0” ก็ได้ ตัวเลือกนี้จะส่งคืนเฉพาะค่าที่ตรงกันทั้งหมดที่เกี่ยวข้องกับการระบุจากตำแหน่งในช่วงคอลัมน์แรก ความล้มเหลวในการค้นหาค่าจากคอลัมน์แรกจะส่งกลับข้อความแสดงข้อผิดพลาด“ # N / A”
# 2 - สูตรการจับคู่
ฟังก์ชัน Match จะส่งคืนตำแหน่งเซลล์ของค่าที่ป้อนสำหรับอาร์เรย์ของตารางที่กำหนด
อาร์กิวเมนต์ทั้งหมดภายในไวยากรณ์เป็นข้อบังคับ
- Lookup_value - ที่นี่อาร์กิวเมนต์ที่ป้อนสามารถเป็นได้ทั้งการอ้างอิงเซลล์ของค่าหรือสตริงข้อความที่มีเครื่องหมายคำพูดคู่ซึ่งจำเป็นต้องดึงตำแหน่งเซลล์
- Lookup_array - ต้องป้อนช่วงอาร์เรย์สำหรับตารางที่ต้องการระบุค่าหรือเนื้อหาเซลล์
- [ประเภทการจับคู่] - อาร์กิวเมนต์นี้มีสามตัวเลือกตามที่อธิบายไว้ด้านล่าง
- “ 1-Less than” -ในที่นี้อาร์กิวเมนต์ที่จะป้อนคือตัวเลข“ 1” ซึ่งจะส่งกลับค่าที่น้อยกว่าหรือเท่ากับค่าการค้นหา นอกจากนี้อาร์เรย์การค้นหาจะต้องเรียงลำดับจากน้อยไปมาก
- "0- ตรงทั้งหมด" - ที่นี่อาร์กิวเมนต์ที่จะป้อนควรเป็นตัวเลข "0" ตัวเลือกนี้จะส่งคืนตำแหน่งที่แน่นอนของค่าการค้นหาที่ตรงกัน อย่างไรก็ตามอาร์เรย์การค้นหาสามารถอยู่ในลำดับใดก็ได้
- “ -1- มากกว่า” - อาร์กิวเมนต์ที่จะป้อนควรเป็นตัวเลข“ -1” ตัวเลือกที่สามค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับค่าการค้นหา ลำดับสำหรับอาร์เรย์การค้นหาจะต้องเรียงลำดับจากมากไปหาน้อย
# 3 - VLOOKUP พร้อมสูตรการแข่งขัน
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
วิธีใช้ VLOOKUP กับ Match Formula ใน Excel
ตัวอย่างด้านล่างจะช่วยให้เข้าใจการทำงานของ vlookup และสูตรการจับคู่เมื่อรวมเข้าด้วยกัน
คุณสามารถดาวน์โหลด VLookup พร้อม Match Excel Template ได้ที่นี่ - VLookup พร้อม Match Excel Templateพิจารณาตารางข้อมูลด้านล่างซึ่งอธิบายถึงข้อมูลจำเพาะของยานพาหนะที่กำหนดที่จะซื้อ
เพื่อให้ได้ความชัดเจนของฟังก์ชันรวมสำหรับ vlookup และฟังก์ชันการจับคู่ให้เราเข้าใจว่าแต่ละสูตรทำงานอย่างไรจากนั้นจึงมาถึงผลการจับคู่ vlookup เมื่อนำมารวมกัน
ขั้นตอน # 1 -ให้เราใช้สูตร vlookup ในระดับบุคคลเพื่อให้ได้ผลลัพธ์
ผลลัพธ์ดังแสดงด้านล่าง:
ที่นี่ค่าการค้นหาเรียกว่า $ B9 ซึ่งเป็นโมเดล“ E” และอาร์เรย์การค้นหาจะได้รับเป็นช่วงของตารางข้อมูลที่มีค่าสัมบูรณ์“ $” ดัชนีคอลัมน์จะเรียกว่าคอลัมน์“ 4” ซึ่งเป็นจำนวนของ คอลัมน์“ ประเภท” และการค้นหาช่วงจะได้รับการจับคู่แบบตรงทั้งหมด
ดังนั้นจึงใช้สูตรต่อไปนี้เพื่อส่งกลับค่าสำหรับคอลัมน์“ เชื้อเพลิง”
ผลลัพธ์ดังแสดงด้านล่าง:
ที่นี่ใช้ค่าการค้นหาด้วยสตริงสัมบูรณ์“ $” สำหรับค่าการค้นหาและ lookup_array จะช่วยแก้ไขเซลล์อ้างอิงแม้ว่าสูตรจะถูกคัดลอกไปยังเซลล์อื่น ในคอลัมน์ "เชื้อเพลิง" เราจำเป็นต้องเปลี่ยนดัชนีคอลัมน์เป็น "5" เป็นค่าที่ข้อมูลจำเป็นในการดึงการเปลี่ยนแปลง
ขั้นตอน # 2 - ตอนนี้ให้เราใช้สูตรการจับคู่เพื่อดึงตำแหน่งสำหรับค่าการค้นหาที่กำหนด
ผลลัพธ์ดังแสดงด้านล่าง:
ดังที่เห็นในภาพหน้าจอด้านบนที่นี่เรากำลังพยายามดึงตำแหน่งคอลัมน์จากอาร์เรย์ตาราง ในกรณีนี้หมายเลขคอลัมน์ที่จะดึงเรียกว่าเซลล์ C8 ซึ่งเป็นคอลัมน์“ ประเภท” และช่วงการค้นหาที่จะค้นหาจะกำหนดเป็นช่วงของส่วนหัวคอลัมน์และประเภทการจับคู่จะได้รับการจับคู่แบบตรงทั้งหมดเป็น“ 0”.
ดังนั้นตารางด้านล่างจะให้ผลลัพธ์ที่ต้องการสำหรับตำแหน่งของคอลัมน์“ เชื้อเพลิง”
ตอนนี้คอลัมน์ที่จะค้นหาจะถูกกำหนดให้เป็นเซลล์ D8 และดัชนีคอลัมน์ที่ต้องการจะกลับมาเป็น“ 5”
ขั้นตอนที่ # 3 - ตอนนี้สูตรการจับคู่จะถูกใช้ในฟังก์ชัน vlookup เพื่อรับค่าจากตำแหน่งคอลัมน์ที่ระบุ
ผลลัพธ์ดังแสดงด้านล่าง:
ในสูตรข้างต้นฟังก์ชันการจับคู่จะถูกใส่แทนพารามิเตอร์ดัชนีคอลัมน์ของฟังก์ชัน vlookup ที่นี่ฟังก์ชันจับคู่จะระบุเซลล์อ้างอิงค่าการค้นหา“ C8”และส่งคืนหมายเลขคอลัมน์ผ่านอาร์เรย์ตารางที่กำหนด ตำแหน่งคอลัมน์นี้จะตอบสนองวัตถุประสงค์เป็นอินพุตของอาร์กิวเมนต์ดัชนีคอลัมน์ในฟังก์ชัน vlookup ซึ่งจะช่วยให้ vlookup ระบุค่าที่จะส่งคืนจากหมายเลขดัชนีคอลัมน์ผลลัพธ์
ในทำนองเดียวกันเราได้ใช้ vlookup กับสูตรการจับคู่สำหรับคอลัมน์ "เชื้อเพลิง" ด้วย
ผลลัพธ์ดังแสดงด้านล่าง:
ดังนั้นเราจึงสามารถใช้ฟังก์ชันการผสมผสานนี้กับคอลัมน์อื่น ๆ “ Type” และ“ Fuel” ได้เช่นกัน
สิ่งที่ต้องจำ
- VLOOKUP สามารถนำไปใช้กับค่าการค้นหาที่ด้านซ้ายสุดเท่านั้น ค่าที่ต้องการค้นหาทางด้านขวาของตารางข้อมูลจะส่งกลับค่าความผิดพลาด“ # N / A”
- ช่วงของ table_array ที่ป้อนในอาร์กิวเมนต์ที่สองควรเป็นการอ้างอิงเซลล์แบบสัมบูรณ์“ $” ซึ่งจะรักษาช่วงอาร์เรย์ของตารางคงที่เมื่อใช้สูตรการค้นหากับเซลล์อื่นหรือมิฉะนั้นเซลล์อ้างอิงสำหรับช่วงอาร์เรย์ของตารางจะเลื่อนไปยังเซลล์ถัดไป เอกสารอ้างอิง.
- ค่าที่ป้อนในค่าการค้นหาไม่ควรน้อยกว่าค่าที่น้อยที่สุดในคอลัมน์แรกของอาร์เรย์ของตารางมิฉะนั้นฟังก์ชันจะส่งคืนค่าความผิดพลาด“ # N / A”
- ก่อนใช้การจับคู่โดยประมาณ“ TRUE” หรือ“ 1” ในอาร์กิวเมนต์สุดท้ายอย่าลืมจัดเรียงอาร์เรย์ของตารางจากน้อยไปมากเสมอ
- ฟังก์ชันการจับคู่จะส่งคืนตำแหน่งของค่าในอาร์เรย์ตาราง vlookup เท่านั้นและไม่ส่งคืนค่า
- ในกรณีที่ Match Function ไม่สามารถระบุตำแหน่งของค่าการค้นหาในอาร์เรย์ของตารางได้จากนั้นสูตรจะส่งกลับ“ # N / A” ในค่าความผิดพลาด
- ฟังก์ชัน Vlookup และการจับคู่ไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่เมื่อจับคู่ค่าการค้นหากับค่าข้อความที่ตรงกันในอาร์เรย์ตาราง