ค้นหาค่าที่ตรงกันรายการสุดท้าย

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

เมื่อรายการเพิ่มขึ้นตามเวลาและคีย์เดียวกันปรากฏหลายครั้ง แถวล่างสุดมักเป็นข้อมูลล่าสุด โดยทั่วไป VLOOKUP หรือ MATCH ที่จับคู่แบบตรงทั้งหมดจะเลือกแถวบนสุดแทน

บทเรียนนี้จะแสดงวิธีที่เชื่อถือได้หลายวิธีในการดึงค่าที่ตรงกัน รายการสุดท้าย

MATCH(value, range, 0) จะสแกนจากบนลงล่างและหยุดที่รายการแรกที่ตรงกันทุกประการ หาก "Apple" ปรากฏในแถวที่ 2, 5 และ 9 MATCH จะส่งคืนค่า 2

วิธีนี้เหมาะอย่างยิ่งเมื่อคีย์ไม่ซ้ำกัน แต่จะไม่สนใจแถวที่ใหม่กว่า หากต้องการไปยังรายการที่ปรากฏล่าสุด เราจำเป็นต้องใช้เทคนิคที่ค้นหาจากด้านล่าง หรือส่งคืนตำแหน่งของรายการสุดท้ายที่ตรงกัน

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

ส่งค่า -1 เป็นอาร์กิวเมนต์โหมดการค้นหาเพื่อค้นหา จากรายการสุดท้ายไปยังรายการแรก จากนั้น XLOOKUP จะส่งคืนค่าที่เชื่อมโยงกับคีย์ที่ตรงกันซึ่งอยู่ล่างสุด

ในตัวอย่างนี้ สูตรจะค้นหาสินค้าใน G1 จากช่วง A2:A10 และส่งคืนราคาที่ตรงกันจาก B2:B10 โดยเริ่มค้นหาจากด้านล่าง

ในตารางคำนวณรุ่นเก่า มีเคล็ดลับที่รู้จักกันดีคือใช้ LOOKUP ร่วมกับเลข 2 และการหารด้วยเงื่อนไขอย่างแยบยล

นิพจน์ 1/(A2:A10=G1) จะให้ค่า 1 สำหรับแถวที่ตรงกัน และให้ข้อผิดพลาดจากการหารสำหรับแถวที่ไม่ตรงกัน LOOKUP ที่ค้นหาเลข 2 ซึ่งมากกว่าค่าที่มีอยู่ทั้งหมด จะข้ามข้อผิดพลาดไปและไปหยุดที่ค่า 1 ที่ถูกต้อง รายการสุดท้าย จากนั้นส่งคืนค่าที่ตรงกันจาก B2:B10

ลองพิจารณา 1/(A2:A10=G1) ทีละขั้น:

• แถวที่คีย์ตรงกันจะให้ค่า 1/TRUE = 1
• แถวที่ไม่ตรงกันจะให้ค่า 1/FALSE = ข้อผิดพลาด #DIV/0!

LOOKUP จะไม่สนใจข้อผิดพลาด และเมื่อไม่พบเป้าหมาย (2) จะส่งคืนผลลัพธ์ที่อยู่ตรงกับรายการที่ไม่ใช่ข้อผิดพลาด รายการสุดท้าย เนื่องจากรายการที่ตรงกันทั้งหมดมีค่าเป็น 1 ค่า 1 รายการสุดท้ายจึงเป็นรายการที่ถูกเลือก และคุณจะได้ค่าจากแถวสุดท้ายที่ตรงกัน

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

ใช้เคล็ดลับการหารแบบเดียวกันภายใน MATCH โดยค้นหาเลข 2 จาก 1/(A2:A10=G1) เพื่อหาตำแหน่งแถวของรายการสุดท้ายที่ตรงกัน จากนั้นส่งตำแหน่งนั้นให้ INDEX ซึ่งทำงานกับคอลัมน์ผลลัพธ์

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

อาร์เรย์ 1/(A2:A10=G1) มีเฉพาะค่า 1 และข้อผิดพลาด ค่าที่มากที่สุดซึ่งไม่เกิน 2 คือ 1 และ MATCH จะส่งคืนตำแหน่งของค่า 1 รายการสุดท้าย ตำแหน่งนั้นตรงกับแถวสุดท้ายที่ตรงกันพอดี

สมมติว่า A2:A10 แสดงสถานะคำสั่งซื้อของ "Order-7" ที่บันทึกตามเวลา และ B2:B10 เก็บข้อความสถานะไว้ "Order-7" ปรากฏในแถวที่ 3, 6 และ 9

• อาร์เรย์การจับคู่จะระบุแถวที่ 3, 6 และ 9 ด้วยค่า 1 และระบุแถวที่เหลือเป็นข้อผิดพลาด
• MATCH(2, ...) ส่งคืนค่า 9 เป็นตำแหน่ง (นับจากจุดเริ่มต้นของช่วง) ซึ่งเป็นรายการสุดท้ายที่ตรงกัน
• INDEX ส่งคืนสถานะจากแถวสุดท้ายนั้น ซึ่งเป็นสถานะล่าสุด

ควรใช้วิธีใด

• XLOOKUP with -1: สะอาดและอ่านง่ายที่สุด หากแอปของคุณรองรับ
• LOOKUP(2, 1/...): ใช้ได้เกือบทุกที่ โดยไม่ต้องใช้เวอร์ชันพิเศษ
• INDEX-MATCH(2, 1/...): มีประโยชน์เมื่อคุณต้องการตำแหน่งด้วย หรือเมื่อต้องการส่งคืนค่าจากคอลัมน์อื่น

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

โปรดระวังประเด็นเหล่านี้:

• ขนาดช่วงไม่ตรงกัน: ช่วงเงื่อนไขและช่วงผลลัพธ์ต้องมีความสูงเท่ากัน มิฉะนั้นแถวจะไม่ตรงกัน
• ค่าซ้ำที่มองไม่เห็น: ช่องว่างท้ายข้อความอาจทำให้ "Apple " แตกต่างจาก "Apple" โปรดล้างข้อความก่อนด้วย TRIM
• ไม่มีรายการที่ตรงกันเลย: เคล็ดลับนี้จะส่งคืนข้อผิดพลาดหากไม่มีรายการตรงกัน ให้ครอบด้วย IFERROR เพื่อแสดงผลสำรองที่เข้าใจง่าย

คุณสามารถใช้เคล็ดลับการค้นหารายการสุดท้ายร่วมกับสองเงื่อนไขได้ ให้คูณการทดสอบเงื่อนไขภายในการหาร เพื่อให้เฉพาะแถวที่ตรงกับคีย์ทั้งสองรายการให้ค่าเป็น 1

ตัวอย่างเช่น ค้นหาราคาล่าสุดที่สินค้าตรงกับ G1 และภูมิภาคตรงกับ G2 เคล็ดลับ LOOKUP(2, ...) ยังคงไปหยุดที่แถวสุดท้ายที่ตรงตามเงื่อนไข

วิธีนี้มีประโยชน์สำหรับบันทึกที่ประทับเวลา ซึ่งสินค้าชนิดเดียวกันปรากฏในหลายภูมิภาค

หากต้องการส่งคืนค่าที่ตรงกัน รายการสุดท้าย แทนรายการแรก:

• ใช้ XLOOKUP(..., -1) เพื่อค้นหาจากล่างขึ้นบนเมื่อมีฟังก์ชันนี้ให้ใช้
• ใช้เคล็ดลับคลาสสิก LOOKUP(2, 1/(range=key), result) ได้กับทุกเวอร์ชัน
• ใช้ INDEX(result, MATCH(2, 1/(range=key))) เมื่อต้องการตำแหน่งด้วย

อย่าลืมให้ช่วงข้อมูลมีขนาดเท่ากัน ล้างช่องว่างส่วนเกิน และครอบสูตรด้วย IFERROR เพื่อความปลอดภัย