Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!?

ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup ข้อมูลแบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่
นั่นคือ หากเราจะหาคำว่า B1 จากตารางที่มีทั้งคำว่า b1 และคำว่า B1
Excel จะเอาผลลัพท์จากคำที่เจอก่อน (วิ่งจากบนลงล่าง) โดยไม่สนว่ามันจะเป็นตัวพิมพ์เดียวกับคำที่เราต้องการหรือไม่ เพราะ Excel มันทำงาน Lookup โดยมองทั้งสองคำนั้นเหมือนๆ กันนั่นเอง

ทางแก้ไข

วิธีที่จะ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่) ได้ จะต้องใช้ ฟังก์ชั่น EXACT และการทำงานของ Array Formula มาช่วย

case-sensitive-lookup

Concept การใส่สูตร

{=INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))}

หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่ก่อน ด้วย MATCH กับ EXACT จากนั้นค่อยใช้ INDEX ดึงค่าที่สอดคล้องออกมา

หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่

  • พื้นฐานคือ ฟังก์ชั่น EXACT ไว้เทียบว่าคำสองคำเหมือนกันทุกประการหรือไม่ โดยแสดงผลกลับมาเป็น TRUE/FALSE
    • หากเราใส่ว่า =EXACT(“B1″,”b1”) จะได้ FALSE เพราะต่างกันตรงตัวพิมพ์เล็กพิมพ์ใหญ่
    • ส่วน =EXACT(“B1″,”B1”) จะได้ TRUE เพราะเหมือนกันทุกประการ
    • หากลองกด F9 ดู จะได้ว่า EXACT(A2:A7,A10)  เป็น {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
  • ที่นี้หากเราเอา TRUE/FALSE มาดำเดินการทางตัวเลขใดๆก็ตาม (เช่น *1 หรือ ใส่ติดลบเข้าไปสองที — ) มันจะกลายเป็นเลข 1 กับ 0
    • หากลองกด F9 ดู จะได้ –EXACT(A2:A7,A10) ออกมาเป็น {0;0;0;1;0;0}
  • จากนั้นเราจึงค่อย MATCH เลข 1 กับผลที่ได้ ว่าอยู่ที่ตำแหน่งแถวที่เท่าไหร่ หากมัน Match กันเจอ แสดงว่า คือคำที่เราต้องการนั่นเอง
    • จะได้ว่า MATCH(1,–EXACT(A2:A7,A10),0) คือ 4 (เพราะ 1 อยู่ลำดับที่ 4)
  • ดังนั้นเมื่อใช้ INDEX มาผสม ก็จะได้ค่าที่ต้องการ
    • =INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))
    • = #VALUE! ได้ Error เพราะลืมบอกว่าเป็น Array Formula
  • เราต้องกด CTRL+SHIFT+ENTER ด้วย (มันจะงอก ปีกกามาให้เอง) เพื่อให้มันทำงานแบบ Array Formula นั่นเองครับ
    • = 40 ถูกต้อง!!