Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!?
ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup แบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่เลยครับ
ดังนั้นถ้าเราหาคำว่า B1 จากตารางที่มีทั้ง b1 และ B1 มันจะหยิบตัวที่เจอก่อน (จากบนลงล่าง) โดยถือว่าเหมือนกันหมด
นี่แหละครับ จุดที่ทำให้หลายคนงงว่าทำไม “พิมพ์ใหญ่/เล็ก” ไม่ตรง แต่ผลลัพธ์ดันได้มาเหมือนเดิม 😅
.
ถ้าอยากให้ Lookup แบบ Case-Sensitive (สนตัวพิมพ์เล็ก/ใหญ่จริงๆ) เราต้องมีตัวช่วยเพิ่มนิดนึงครับ
ทางแก้ไข
หัวใจคือใช้ EXACT เทียบคำแบบตัวต่อตัว แล้วค่อยดึงผลลัพธ์ด้วย INDEX/MATCH แบบเดิม หรือใช้สูตรใหม่ใน Excel 365 ที่ไม่ต้องกด Ctrl+Shift+Enter ก็ได้ครับ
Concept การใส่สูตร
สูตรใหม่ใน Excel 365 (ไม่ต้อง CSE)
ถ้าใช้ Excel 365 สูตรพวกนี้อ่านง่าย แถมกด Enter ได้เลยครับ
- FILTER + EXACT (ได้รายการทั้งหมดที่ตรงแบบตัวพิมพ์เล็ก/ใหญ่)
=FILTER(B2:B7,EXACT(A2:A7,A10),"ไม่เจอ") - XLOOKUP + EXACT (อยากได้ตัวแรกที่ตรงแบบเคส)
=XLOOKUP(TRUE,EXACT(A2:A7,A10),B2:B7,"ไม่เจอ",0) - TAKE + FILTER (เอาตัวสุดท้ายแบบเคส)
=TAKE(FILTER(B2:B7,EXACT(A2:A7,A10),"ไม่เจอ"),-1) - XMATCH + INDEX (ถนัดสาย MATCH รุ่นใหม่)
=INDEX(B2:B7,XMATCH(TRUE,EXACT(A2:A7,A10),0))
สังเกตว่า KEY จริงๆ คือ EXACT ที่ทำให้เป็น Case-Sensitive ส่วนฟังก์ชันอื่นเป็นตัวช่วยดึงผลลัพธ์ให้ง่ายขึ้นครับ
สูตรดั้งเดิม (Excel รุ่นเก่า ต้องกด Ctrl+Shift+Enter)
{=INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))}
หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่
- พื้นฐานคือฟังก์ชัน 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(1,–EXACT(A2:A7,A10),0) คือ 4 (เพราะเลข 1 อยู่ลำดับที่ 4)
- แล้วค่อยใช้ INDEX ดึงค่าที่สอดคล้องออกมา
- =INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))
- = #VALUE! ได้ Error เพราะลืมบอกว่าเป็น Array Formula (เฉพาะ Excel รุ่นเก่า)
- Excel รุ่นเก่าต้องกด CTRL+SHIFT+ENTER ด้วยให้มันมีปีกกา แต่ถ้าเป็น Excel 365 กด Enter ปกติได้เลยครับ
- = 40 ถูกต้อง!!