รวม INDEX และ MATCH

ตอนนี้คุณรู้จักองค์ประกอบสองส่วนของการค้นหาแล้ว MATCH ค้นหาว่าค่าอยู่ ที่ไหน ส่วน INDEX จะส่งกลับค่าที่อยู่ ณ ตำแหน่งนั้น

เมื่อนำมารวมกัน คุณจะได้การค้นหาที่สมบูรณ์ MATCH จะระบุแถว จากนั้น INDEX จะดึงข้อมูลจากแถวนั้นในคอลัมน์ใดก็ได้ที่คุณเลือก

รูปแบบนี้เข้าใจได้ง่ายเมื่อมองเห็นแนวคิด: วาง MATCH ไว้ ภายใน INDEX ตรงตำแหน่งที่ปกติใช้ระบุหมายเลขแถว

นี่คือรูปแบบที่คุณจะใช้ซ้ำแล้วซ้ำอีก:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

ให้อ่านจากด้านในออกมาด้านนอก MATCH จะทำงานก่อนและส่งกลับตัวเลขตำแหน่ง จากนั้นตัวเลขนั้นจะกลายเป็น หมายเลขแถว สำหรับ INDEX ซึ่งจะส่งกลับค่าจากช่วงผลลัพธ์ของคุณ

โดยทั่วไปช่วงผลลัพธ์และช่วงค้นหาจะมีจำนวนแถวเท่ากัน ดังนั้นตำแหน่งในช่วงหนึ่งจึงตรงกับตำแหน่งในอีกช่วงหนึ่ง

ลองนึกภาพตารางที่คอลัมน์ A มีชื่อผลิตภัณฑ์และคอลัมน์ C มีราคา คุณต้องการราคาของ "เชอร์รี"

ขั้นแรก MATCH จะค้นหาเชอร์รี: =MATCH("Cherry", A2:A20, 0) ซึ่งสมมติว่าส่งกลับ 3

จากนั้น INDEX จะใช้ค่า 3 นั้น: =INDEX(C2:C20, 3) เพื่อส่งกลับราคาในแถวที่ 3 ของคอลัมน์ C

เมื่อซ้อนฟังก์ชันทั้งสองเข้าด้วยกัน คุณจะได้ผลลัพธ์ในครั้งเดียว: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0))

การกำหนดค่า "เชอร์รี" ไว้ตายตัวเหมาะสำหรับการเรียนรู้ แต่สูตรจริงควรอ้างอิงเซลล์แทน ให้ใส่คำค้นหาใน E1 แล้วอ้างอิงเซลล์นั้น

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

ตอนนี้ไม่ว่าคุณจะพิมพ์ผลิตภัณฑ์ใดใน E1 ระบบก็จะส่งกลับราคาของผลิตภัณฑ์นั้นทันที พิมพ์กล้วยเพื่อดูราคากล้วย หรือพิมพ์อินทผลัมเพื่อให้คำตอบอัปเดต

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

นี่คือเคล็ดลับที่ทำให้ INDEX-MATCH มีความพิเศษ ช่วงคอลัมน์ค้นหาและช่วงคอลัมน์ส่งคืนข้อมูลแยกจากกัน ดังนั้นค่าที่คุณส่งคืนจึงอยู่ทางซ้ายของค่าที่คุณค้นหาได้

สมมติว่าราคาอยู่ในคอลัมน์ A และชื่อสินค้าอยู่ในคอลัมน์ C หากต้องการค้นหาราคาสินค้าจากชื่อ ให้เขียน =INDEX(A2:A20, MATCH(E1, C2:C20, 0))

คุณค้นหาในคอลัมน์ C แต่ส่งคืนข้อมูลจากคอลัมน์ A ส่วน VLOOKUP ไม่สามารถทำเช่นนี้ได้หากไม่มีตัวช่วย

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

ค้นหาอีเมลของลูกค้า: =INDEX(D2:D50, MATCH(E1, A2:A50, 0))

หากต้องการค้นหาเมืองของลูกค้าคนเดิมแทน: =INDEX(F2:F50, MATCH(E1, A2:A50, 0))

ส่วน MATCH ยังคงเหมือนเดิม มีเพียงช่วงของ INDEX เท่านั้นที่เปลี่ยนเพื่อเลือกคำตอบอื่น

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

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

MATCH ตัวแรกค้นหาแถวจากป้ายกำกับในคอลัมน์ A ส่วนตัวที่สองค้นหาคอลัมน์จากหัวตารางในแถวที่ 1 จากนั้น INDEX จะส่งคืนเซลล์ที่ทั้งสองตำแหน่งตัดกัน รูปแบบขั้นสูงนี้จะอธิบายโดยละเอียดในภายหลัง

เพื่อให้ตำแหน่งตรงกัน ช่วงค้นหาและช่วงส่งคืนข้อมูลของคุณต้องเริ่มต้นที่แถวเดียวกันและมีความสูงเท่ากัน

หาก MATCH ค้นหาใน A2:A20 (19 แถว) แต่ INDEX ส่งคืนข้อมูลจาก C2:C19 (18 แถว) ตำแหน่งจะคลาดเคลื่อนและคุณจะได้คำตอบที่ไม่ถูกต้อง

แนวทางที่เชื่อถือได้คือ ใช้ช่วงแถวเดียวกันทุกประการกับทั้งสองช่วง เช่น A2:A20 และ C2:C20 การอ้างอิงทั้งคอลัมน์อย่าง A:A และ C:C ก็จะรักษาแนวให้ตรงกันโดยอัตโนมัติเช่นกัน

หาก MATCH ไม่พบค่าที่ใช้ค้นหา ระบบจะส่งคืน #N/A และ INDEX-MATCH ทั้งหมดจะแสดงข้อผิดพลาดนั้น ให้ครอบสูตรด้วย IFNA เพื่อกำหนดค่าทดแทนที่เรียบร้อย

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

เมื่อทำเช่นนี้ สินค้าที่ค้นหาไม่พบจะแสดงข้อความ "Not found" แทนข้อผิดพลาดที่ดูน่ากังวล IFERROR ก็ใช้ได้เช่นกัน แต่ IFNA จะจัดการเฉพาะกรณีที่ไม่พบข้อมูล และปล่อยให้ข้อผิดพลาดประเภทอื่นแสดงออกมา

ลองนำทุกอย่างมารวมกัน คุณมีตารางพนักงานที่มีรหัสอยู่ในคอลัมน์ A ชื่ออยู่ใน B แผนกอยู่ใน C และเงินเดือนอยู่ใน D ผู้ใช้กรอกรหัสลงใน G1

หากต้องการส่งคืนแผนกของพนักงานคนนั้น: =INDEX(C2:C200, MATCH(G1, A2:A200, 0))

หากต้องการส่งคืนเงินเดือนแทน ให้เปลี่ยนช่วงของ INDEX เป็น D2:D200 ตรรกะการค้นหาไม่เปลี่ยนแปลง มีเพียงคอลัมน์ที่คุณอ่านข้อมูลเท่านั้นที่เปลี่ยน นี่คือเครื่องมือหลักที่ใช้ในงานประจำวันสำหรับการค้นหาแบบไดนามิก

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

สำหรับ =INDEX(C2:C20, MATCH(E1, A2:A20, 0)) ให้อ่าน MATCH(E1, A2:A20, 0) ก่อน ลองนึกภาพว่าฟังก์ชันนี้ส่งคืนตัวเลขอย่าง 5 จากนั้นแทนค่าลงไปในใจเพื่อให้ได้ =INDEX(C2:C20, 5)

ทันใดนั้น สูตรก็เป็นเพียง "ส่งคืนราคาลำดับที่ 5" นิสัยนี้ช่วยให้การแก้ไขข้อผิดพลาดของการค้นหาแบบซ้อนทุกชนิดเป็นเรื่องง่าย

ยืนยันว่าคุณเข้าใจวิธีที่ฟังก์ชันทั้งสองทำงานร่วมกัน

คุณนำฟังก์ชันทั้งสองมารวมกันเป็นการค้นหาที่ยืดหยุ่น:

• รูปแบบ: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH ค้นหาตำแหน่งของแถว ส่วน INDEX จะส่งคืนค่าที่ตำแหน่งนั้น
• คอลัมน์ค้นหาและคอลัมน์ส่งคืนข้อมูลแยกจากกัน จึงค้นหาไปทางซ้ายได้ง่ายพอ ๆ กับทางขวา
• รักษาความสูงของทั้งสองช่วงให้เท่ากัน และครอบสูตรด้วย IFNA เพื่อจัดการข้อผิดพลาดอย่างเรียบร้อย

ต่อไป มาดูกันว่าเหตุใดแนวทางนี้จึงมักดีกว่า VLOOKUP