Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!?
ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup ข้อมูลแบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่
นั่นคือ หากเราจะหาคำว่า B1 จากตารางที่มีทั้งคำว่า b1 และคำว่า B1
Excel จะเอาผลลัพท์จากคำที่เจอก่อน (วิ่งจากบนลงล่าง) โดยไม่สนว่ามันจะเป็นตัวพิมพ์เดียวกับคำที่เราต้องการหรือไม่ เพราะ Excel มันทำงาน Lookup โดยมองทั้งสองคำนั้นเหมือนๆ กันนั่นเอง
ทางแก้ไข
วิธีที่จะ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่) ได้ จะต้องใช้ ฟังก์ชั่น EXACT และการทำงานของ Array Formula มาช่วย
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 ถูกต้อง!!
