สำหรับผู้ที่มีความรอบรู้ใน Excel เป็นไปได้ว่าคุณคุ้นเคยกับฟังก์ชั่น VLOOKUPมากที่สุด ฟังก์ชัน VLOOKUPใช้เพื่อค้นหาค่าในเซลล์อื่นตามข้อความที่ตรงกันภายในแถวเดียวกัน
หากคุณยังใหม่กับ VLOOKUPฟังก์ชั่นคุณสามารถตรวจสอบโพสต์ก่อนหน้าของฉันใน วิธีใช้ VLOOKUP ใน Excel.
มีประสิทธิภาพเท่าที่เป็น VLOOKUPมีข้อ จำกัด เกี่ยวกับวิธีการกำหนดตารางอ้างอิงที่ตรงกันเพื่อให้สูตรทำงาน
บทความนี้จะแสดงข้อ จำกัด ที่ VLOOKUPไม่สามารถใช้และแนะนำฟังก์ชั่นอื่นใน Excel ที่เรียกว่า INDEX-MATCHที่สามารถแก้ปัญหาได้
ตัวอย่าง INDEX MATCH Excel
การใช้ ต่อไปนี้ 2เรามีรายชื่อเจ้าของรถและชื่อรถยนต์ ในตัวอย่างนี้เราจะพยายามคว้า รหัสประจำรถตาม โมเดลรถที่แสดงรายการภายใต้เจ้าของหลายคนดังแสดงด้านล่าง:
ในแผ่นแยกต่างหากที่ชื่อว่า CarTypeเรามีฐานข้อมูลรถยนต์ที่เรียบง่ายพร้อม ID, โมเดลรถและ สี.
ด้วยการตั้งค่าตารางนี้ ฟังก์ชั่น VLOOKUPสามารถทำงานได้ต่อเมื่อข้อมูลที่เราต้องการเรียกคืนอยู่ในคอลัมน์ทางด้านขวาของสิ่งที่เราพยายามจับคู่ (ช่อง โมเดลรถยนต์)
กล่าวอีกนัยหนึ่งด้วยโครงสร้างตารางนี้เนื่องจากเราพยายามจับคู่ตาม โมเดลรถข้อมูลเดียวที่เราจะได้คือ สี(ไม่ใช่ IDเนื่องจากคอลัมน์ IDตั้งอยู่ทางด้านซ้ายของ รุ่นรถยนต์column.)
ซึ่งเป็นเพราะ ด้วย VLOOKUP ค่าการค้นหาจะต้องปรากฏในคอลัมน์แรกและคอลัมน์การค้นหาจะต้องอยู่ทางด้านขวา ไม่มีเงื่อนไขใดที่ตรงตามในตัวอย่างของเรา
ข่าวดีก็คือ INDEX-MATCHจะสามารถช่วยให้เราบรรลุเป้าหมายนี้ได้ ในทางปฏิบัตินี่เป็นการรวมฟังก์ชั่นของ Excel สองตัวที่สามารถทำงานแยกกัน: ฟังก์ชั่น INDEXและฟังก์ชั่น MATCH
อย่างไรก็ตามเพื่อจุดประสงค์ของบทความนี้เราจะพูดคุยเกี่ยวกับการรวมกันของทั้งสองโดยมีวัตถุประสงค์เพื่อจำลองฟังก์ชันของ VLOOKUP
สูตรอาจดูยาวและน่ากลัวในตอนแรก อย่างไรก็ตามเมื่อคุณใช้หลาย ๆ ครั้งคุณจะได้เรียนรู้ไวยากรณ์ด้วยใจ
นี่คือสูตรเต็มรูปแบบในตัวอย่างของเรา:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
ที่นี่ คือรายละเอียดของแต่ละส่วน
= INDEX (- “ =”ระบุจุดเริ่มต้นของสูตรในเซลล์และ INDEXเป็นส่วนแรกของฟังก์ชัน Excel ที่เราใช้อยู่
CarType! $ A $ 2: $ A $ 5- คอลัมน์บนแผ่นงาน CarTypeซึ่งมีข้อมูลที่เราต้องการเรียกคืน ในตัวอย่างนี้ IDของ โมเดลรถยนต์แต่ละรุ่น
การจับคู่ (- ส่วนที่สองของฟังก์ชัน Excel ที่ เรากำลังใช้
B4- เซลล์ที่มีข้อความค้นหาที่เราใช้ (รุ่นรถยนต์) ./ p>
CarType! $ B $ 2: $ B $ 5- คอลัมน์บนแผ่นงาน CarTypeพร้อมข้อมูลที่เราจะใช้จับคู่กับข้อความค้นหา
0))- เพื่อระบุว่าข้อความค้นหาจะต้องตรงกับข้อความในคอลัมน์ที่ตรงกัน (เช่น CarType! $ B $ 2: $ B $ 5) หากไม่พบการจับคู่ที่ตรงกันสูตรจะส่งคืน #N/A
หมายเหตุ: โปรดจำวงเล็บปิดคู่ไว้ท้ายสุด ของฟังก์ชันนี้ “))”และเครื่องหมายจุลภาคระหว่างอาร์กิวเมนต์
โดยส่วนตัวแล้วฉันย้ายออกจาก VLOOKUP แล้วตอนนี้ใช้ INDEX-MATCH เพราะสามารถ ทำมากกว่า VLOOKUP
ฟังก์ชั่น INDEX-MATCHก็มีประโยชน์อื่น ๆ เมื่อเปรียบเทียบกับ VLOOKUP
เมื่อเราทำงานกับชุดข้อมูลขนาดใหญ่ซึ่งการคำนวณเองอาจใช้เวลานานเนื่องจากฟังก์ชัน VLOOKUP จำนวนมากคุณจะพบว่าเมื่อคุณแทนที่ทั้งหมด ของสูตรที่มี INDEX-MATCH การคำนวณโดยรวมจะคำนวณได้เร็วขึ้น
หากตารางอ้างอิงของเรามีข้อความสำคัญที่เราต้องการค้นหาในคอลัมน์ Cและข้อมูลที่เราต้องได้รับคือ คอลัมน์ AQเราจะต้องทราบ / นับจำนวนคอลัมน์ที่อยู่ระหว่างคอลัมน์ C และคอลัมน์ AQ เมื่อใช้ VLOOKUP
ด้วยฟังก์ชัน INDEX-MATCH เราสามารถเลือก คอลัมน์ดัชนี (เช่นคอลัมน์ AQ) ที่เราต้องการรับข้อมูลและเลือกคอลัมน์ที่จะจับคู่ (เช่น คอลัมน์ C).
VLOOKUP ค่อนข้างธรรมดาในปัจจุบัน แต่ไม่มากนัก รู้เกี่ยวกับการใช้ฟังก์ชั่น INDEX-MATCH ด้วยกัน
สตริงที่ยาวกว่าในฟังก์ชัน INDEX-MATCH ช่วยให้คุณดูเหมือนผู้เชี่ยวชาญในการจัดการกับฟังก์ชัน Excel ที่ซับซ้อนและขั้นสูง ขอให้สนุก!