INDEX กับ MATCH : ทลายข้อจำกัดของ VLOOKUP

วันก่อนพอดีมีเพื่อนคนหนึ่งพูดถึงฟังก์ชั่น VLOOKUP ขึ้นมา เลยนึกสนุกอยากเอาเรื่องที่เกี่ยวกับสูตรนี้มาลองเขียนดู

สำหรับการทำงานกับข้อมูล แล้ว VLOOKUP นี่ถือเป็นสูตรที่เจ๋งมากๆ ตัวนึง และมีคนใช้ในการดึงข้อมูลจากตารางเยอะมาก แต่ในตัวมันเองก็ยังมีข้อจำกัดบางประการ* เช่น:

1. ข้อมูลที่จะเอากลับมาต้องอยู่ทางด้านขวาของ Column ที่เราจะค้นหาเท่านั้น
   เช่นจากรูปตัวอย่างด้านล่างนี้ หากผมต้องการการค้นข้อมูลโดยใช้ตัวอักษรย่อ 2 ตัว เพื่อหาข้อมูลจากใน Column B .. ผมสามารถใช้ VLOOKUP ได้หากค่าที่ผมอยากได้อยู่ใน Column C/D/E
   แต่ถ้าอยากได้ชื่อประเทศจาก Column A ก็จะทำไม่ได้ เพราะ VLOOKUP จะใช้ข้อมูลจาก Column ซ้ายสุดเป็นตัวตั้งต้นเท่านั้น

2. ถ้ามีการแทรก Column เพิ่มเข้าไป อาจจะทำให้ VLOOKUP มีการ return ค่าจาก Column ที่ผิดได้ ตัวอย่างเช่น ในสูตรด้านล่างนี้ ผมค้นข้อมูลด้วยตัวอักษร 2 ตัว และต้องการได้ค่า Code ตัวเลขคืนมา ซึ่งก็คือ Column Index ที่ 3
   พอมีการแทรก Column เข้าไปปุ๊บ.. ผลที่ได้จาก VLOOKUP ก็จะผิดทันที
   

หมายเหตุ: จริงๆ ข้อจำกัดที่กล่าวมานี้ ก็พอจะมีวิธีแก้โดยยังใช้ VLOOKUP ร่วมกับ Formula อื่นอยู่ แต่ว่าจะค่อนข้างซับซ้อนไปหน่อย

---

ด้วยข้อจำกัดดังกล่าวของ VLOOKUP เราสามารถหันมาใช้ INDEX กับ MATCH แก้ปัญหาแทนได้ครับ

(พอเห็นเป็นสูตร 2 ตัว ก็อย่าเพิ่งตกใจ เพราะว่าจริงๆ แล้วมันเข้าใจง่ายมาก!)

ก่อนอื่น เพื่อให้เห็นภาพได้ง่ายขึ้น สามารถ Download ไฟล์ตัวอย่าง ที่อธิบายการใช้งาน INDEX & MATCH ทีละขั้นๆ ไปลองดูได้เลยครับ

ไฟล์ตัวอย่าง INDEX_MATCH_STEP_BY_STEP Tutorial

MATCH

MATCH จะใช้สำหรับค้นหาในช่วงของเซลล์ต่างๆ จากนั้นก็จะบอกได้ว่า ไอ้ค่าที่เราต้องการนั้น อยู่ในลำดับที่เท่าไหร่ของแถวข้อมูล
โดยวิธีการใช้… ที่ถ้าหากแปลงเป็นคำพูด น่าจะประมาณนี้ (เพิ่มเติม)

=Match( <ค่าที่จะใช้สำหรับหา> ,  <แถวของข้อมูลที่จะค้นหา> , <ค้นหาแบบไหน> )

ในที่นี้ <ค้นหาแบบไหน> จะใช้เป็น 0 คือ Exact Match ครับ เพราะเราต้องการค่าที่มันใช่เป๊ะๆ จริงๆ
ตัวอย่างเช่น ข้างล่างนี้ ผมใช้ MATCH ในการหาข้อมูลใน Column B…

=Match(  หา “AS”  ,  จากเซลส์ B2 ถึง B250 , หาแบบ Exact Match… เอาแบบเป๊ะๆ เลยนะ )

มันก็จะบอกกลับมาว่า.. ไอ้ที่ผมหาว่า AS เนี่ย… พอไปค้นดูแล้ว มันอยู่ลำดับที่ 5 นะ… โอเคป้ะ?

..

INDEX

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

วิธีใช้สำหรับในกรณีของบทความนี้* ถ้าพูดเป็นคำพูด ได้ประมาณนี้ (เพิ่มเติม):

=INDEX( <แถวของข้อมูล> , <ตำแหน่งที่เท่าไหร่?> )

จากตัวอย่างข้างล่าง ผมบอกว่าจะเอาข้อมูลจากช่วงเซลส์ D2 ถึง D250, เอาตำแหน่งที่ 5

ก็จะได้กลับมาเป็น 016

ใช้ INDEX ร่วมกับ MATCH

จะเห็นว่า หลังจากเราใช้ MATCH ใน Column ที่เราจะค้นหา ว่าค่าที่จะค้นหามันอยู่ตำแหน่งที่เท่าไหร่ได้แล้ว

เราก็แค่ใช้ INDEX กับอีก Column หนึ่งที่เราอยากได้ค่ากลับคืนมา โดยระบุตำแหน่งที่ได้มาจาก Match นั่นแหละ

โดยจากตัวอย่างก่อนหน้า แทนที่จะระบุในสูตร INDEX ว่าเอาค่าจากตำแหน่งที่ ‘5’ ตรงๆ เราก็แค่เปลี่ยนไปเอาค่าที่ได้จาก MATCH มาใช้แทน เพียงเท่านี้ เราก็จะได้สูตร INDEX & MATCH ที่ทำงานร่วมกันได้เหมือน VLOOKUP แล้วครับ

ทีนี้ ถ้าเราอยากจะได้ข้อมูลจาก Column A แทน ก็แค่เปลี่ยนแถวข้อมูลที่เราจะใช้กับ INDEX ไปที่ Column A แทน

(ถ้ารูปไม่ชัด ให้คลิ้กที่รูปเพื่อเปิดดูขนาดเต็ม)

และเพื่อประหยัดพื้นที่ เราสามารถจับเอา MATCH มายัดไส้ไว้ใน INDEX ตรงๆ ไปเลย

ได้เป็น:

---

ด้วยวิธีนี้ เราก็จะสามารถใช้ INDEX และ MATCH แทน VLOOKUP ได้ และยังสามารถเรียกขอคืนค่าที่อยู่ใน Column ด้านซ้ายมือของแถวข้อมูลที่เราจะ Lookup ได้อีกด้วย

แล้วถ้าวันใดวันหนึ่ง ตำแหน่ง Column ของในตารางต้องมีการต้องเปลี่ยนแปลง เช่น เพิ่ม Column เข้ามา เราก็ไม่ต้องกลัวว่าสูตรนี้จะมีปัญหา เพราะมัน Fix Column ไว้อยู่แล้ว