Index and Match เป็นฟังก์ชันที่ใช้ดึงข้อมูลอื่นมาแสดงที่น่าสนใจอีกอันหนึ่งนอกเหนือจาก vlookup ซึ่งหลายคนคิดว่ามันซับซ้อนไปหน่อย เพราะต้องซ้อนฟังก์ชันกันสองอย่าง คือ index กับ match ในขณะที่ vlookup เสร็จครบจบในฟังก์ชันเดียว แต่ถ้าเข้าใจแนวคิดการใช้ก็ไม่ยาก ใช้บ่อย ๆ ก็จะคุ้น และมันสามารถยืดหยุ่นได้ดีกว่า vlookup ในบางกรณี
ปัจจุบัน Excel มีฟังก์ชันใหม่เรียกว่า Xlookup ที่ใช้งานได้สะดวกกว่า แต่ยังจำกัดอยู่กับ Excel เวอร์ชัน 2019 เป็นต้นมาเท่านั้น หากคิดว่า ยังต้องเขียนสูตรเพื่อใช้งานร่วมกับบุคคลอื่นหรือมีความจำเป็นต้องนำไปเปิดกับ Excel เวอร์ชันเก่ากว่านั้น การใช้ Index & Match ยังคงเป็นทางเลือกที่ดี
ก่อนอื่น มาดูที่ฟังก์ชันแต่ละตัวมีหลักการทำงานอย่างไร
เนื้อหาโดยรวม :)
ฟังก์ชัน Index
เป็นฟังก์ชันสำหรับดึงข้อมูลจากตารางที่เราร้องการ โดยจะต้องระบุเป็นตัวเลขลำดับแถว และลำดับคอลัมน์
สูตรการใช้คือ
INDEX(array, row_num, [column_num])
ฟังก์ชัน INDEX ส่งคืนค่าในอาร์เรย์โดยยึดตามหมายเลขแถวและคอลัมน์ที่ระบุ
Array คือช่วงของข้อมูลที่ต้องการหา
row_num คือลำดับแถวที่ต้องการหา
column_num คือลำดับคอลัมน์ที่ต้องการหา
ถ้าหากช่วงที่เราจะหามีคอลัมน์หรือแถวเดียว เราใส่เพียงแค่ค่าเดียวก็ได้ มันจะตีความเป็นแถวหรือคอลัมน์ให้โดยอัตโนมัติ
ตัวอย่างเช่น ใช้ฟังก์ชัน INDEX(A2:A55,4) หมายถึง ให้ดูใน cell ระหว่าง A2 ถึง A55 แล้วส่งผลลัพธ์ค่าที่อยู่ในแถวที่ 4 กรณีการหาจากคอลัมน์เดียวแบบนี้ (ในตัวอย่างคือคือคอลัมน์ A) ไม่จำเป็นต้องใส่ลำดับคอลัมน์ในสูตร เพราะมีคอลัมน์เดียวอยู่แล้ว ผลลัพธ์ที่ได้ก็คือ 71695
INDEX(A2:A55,4
อย่าลืม row_num ในที่นี้จะนับตามลำดับจากช่วงที่เลือก ไม่ใช่ตำแหน่ง cell เช่นถ้าสูตรเป็น INDEX(A4:A55,4) จะได้ผลลัพธ์เป็น 58337 เพราะจะนับ cell A4 เป็นลำดับที่ 1
อีกทางหนึ่ง ถ้าช่วงที่มองหาไม่ได้เป็นคอลัมน์ แต่เป็นแถว ค่าที่ใส่ไปเพียงตัวเดียวจะหมายถึงลำดับตามแถวโดยอัตโนมัติ เช่น INDEX(A2:E2,3) หมายถึง ให้ดูใน cell ระหว่าง A2 ถึง E2 แล้วส่งผลลัพธ์ค่าที่อยู่ในลำดับที่ 3 ก็คือ บางเขนฝั่งใต้
กรณีช่วงที่ต้องการหามีทั้งแถวและคอลัมน์ เช่น ใช้สูตร INDEX(A2:E55,6,2) หมายถึง ให้ดูใน cell ระหว่าง A2 ถึง E55 แล้วส่งค่าที่อยู่ใน Cell ที่อยู่แถวที่ 6 คอลัมน์ที่ 2 ซึ่ง ก็คือ 959
ถ้าใส่ไม่ครบผลลัพธ์จะขึ้น #Ref หรือการอ้างอิงผิดพลาด เช่นจากตัวอย่างเรามีแค่ 5 คอลัมน์ (คือ A B C D E) ถ้าเราใส่ 10 แทน 2 จะขึ้น #Ref หรือการอ้างอิงผิดพลาด เพราะมีแค่ 5 คอลัมน์แต่ให้ไปแสดงค่าในคอลัมน์ที่ 10 ซึ่งไม่มีในช่วงที่เรากำหนด
นี่คือคอนเซ็ปต์การทำงานของฟังก์ชัน index
แต่ index เป็นแค่การแสดงค่าที่ต้องการ แต่ถ้าเราไม่รู้ว่าค่านั้นอยู่ในลำดับที่เท่าไหร่ จะต้องทำอย่างไร ในกรณีที่ไม่สามารถเจาะจงว่าอยู่ในแถวหรือคอลัมน์ใด เราก็ต้องนำฟังก์ชัน Match ไปใช้ร่วมด้วยเพื่อใช้ส่งค่าลำดับของแถวหรือคอลัมน์
ฟังก์ชัน Match
หมายเหตุ ทาง Excel ออกฟังก์ชันใหม่ XMatch แต่ใช้กับ Excel เวอร์ชัน 2019 เป็นต้นไปเท่านั้น
สูตรการใช้คือ MATCH(lookup_value, lookup_array, [match_type])
ฟังก์ชัน MATCH จะค้นหารายการที่ระบุใน ช่วง ของเซลล์ต่างๆ จากนั้นจะส่งกลับตำแหน่งสัมพันธ์ของรายการดังกล่าวในช่วงนั้น
lookup_value คือค่าที่ต้องการหา
lookup_array คือช่วงที่ต้องการหา
match_type คือประเภทการแสดงค่าที่หาได้ ถ้าเป็น 1 หรือละไว้จะค้นหาค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับ lookup_value ถ้าเป็น -1 จะค้นหาค่าที่มากที่สุดซึ่งมากกว่าหรือเท่ากับ lookup_value แต่ทั้งสองกรณีนี้ค่าใน lookup_array จะต้องเรียงลำดับจากน้อยไปมาก ไม่ใช่นั้นจะแสดงผลคลาดเคลื่อนจากที่ตั้งใจ)
เช่น ถ้าใช้สูตร MATCH(9651,A2:A55,1) หมายถึง ค้นหาค่า 9651 จากช่วง A2 ถึง A55 โดยจะเป็นค่าที่ตรงกันพอดีหรือถ้าไม่มีจะเป็นค่าที่มากที่สุดซึ่งน้อยกว่าค่าที่ต้องการค้นหา ซึ่งผลลัพธ์จะได้ 6 ทั้งที่มีค่า 9651 ในช่อง A4 ซึ่งควรส่งกลับค่าเป็น 3
แต่สังเกตหรือไม่ว่าลำดับมันผิด เพราะไม่ได้เรียงลำดับจากน้อยไปมาก ซึ่งเป็นปัญหาแบบเดียวกับ Vlookup ที่เคยเขียนถึงไปแล้ว (อ่านได้ที่ เมื่อ Vlookup แสดงค่า error #n/a และ ระวัง Vlookup แสดงผลลัพธ์ผิด )
ดังนั้น ถ้าต้องการค่าที่ตรงกันเท่านั้น ต้องใช้ match_type เป็น 0 หรือปล่อยค่าว่างเอาไว้ไม่ต้องไปใส่มัน จะค้นหาค่าที่เท่ากับ lookup_value เท่านั้น ถ้ามีค่านั้นมากกว่า 1 ตัว จะแสดงลำดับแรกที่พบเช่น MATCH(0,F2:F20,0) จะได้ผลลัพธ์เป็น 1 เพราะเป็นลำดับของเลข 0 ตัวแรกที่พบ
ใช้ Index ร่วมกับ Match
จะเห็นความสัมพันธ์ที่เอามาใช้ได้ก็คือ เราใช้ Match เพื่อส่งค่ากลับมาว่าอยู่ในลำดับที่เท่าไหร่ แล้วเอาค่าที่ได้ไปใช้กับ index เพื่อแสดงค่าที่ได้จากลำดับนั้น ถ้าจะแปลงให้เข้าสูตรก็จะเป็น
Index (คอลัมน์ที่จะส่งค่าจาก,match (ค่าที่ต้องการหา, คอลัมน์ที่จะค้นหา,0))
สำหรับคนที่ยังคิดว่า Index and Match มันยุ่งยาก ลองปรับสูตรการใช้งานโดยแยกทีละขั้นตอนให้ดูง่าย ๆ ก็ได้ และจะแสดงให้เห็นการใช้งานที่สะดวกอย่างหนึ่งที่เคยเอ่ยถึงไปแล้วคือ
ตัวอย่างเช่น ถ้าต้องการหา โฉนดเลขที่ 9651 จากข้อมูลที่อยู่ในคอลัมน์ B แล้วให้แสดงผลเป็นราคา ซึ่งอยู่ในคอลัมน์ F ก็จะได้แนวคิดออกมาแบบนี้
การแสดงผลลัพท์ จะใช้ index ซึ่งการส่งกลับราคาจากคอลัมน์ F ดังนั้นจะเขียนสูตรได้ว่า
INDEX(F2:F55,X)
นั่นคือเราจะหาค่าจากช่วง F2 ถึง F20 แต่จะแสดงค่าในลำดับที่เท่าไหร่? เรายังไม่รู้ เพราะต้องหาก่อนว่าค่า 9651 มันอยู่ในลำดับที่เท่าไหร่? ดังนั้นแทนค่าด้วย X ไปก่อน คือ ไม่รู้ว่าจะให้ค้นหาจากค่าลำดับที่เท่าไหร่ ถึงจะมีโฉนดเท่ากลับเลข 9651
ตรงนี้จะต้องใช้ match หาว่า 9651 อยู่ในลำดับที่เท่าไหร่ จะได้สูตร MATCH(9651,B2:B55,0) คือ ค้นหา 9651 จากคอลัมน์ B ซึ่งถ้าใช้ฟังก์ชันนี้อย่างเดียวจะได้ผลลัพธ์กลับมาเป็น 3 ตามตัวอย่างข้างต้น
เมื่อเอาสองฟังก์ชันมาเรียงต่อกันก็จะได้
INDEX(F2:F55,MATCH(9651,B2:B55,0))
ซึ่งตรงนี้ก็จะมีคนสงสัย ทำไมเราต้องยุ่งยากหาฟังก์ชันสองอย่างมาใช้ แทนที่จะใช้ vlookup ให้มันจบครบที่เดียว ด้วยสูตร =VLOOKUP(9651,B2:F55,5,0)
อันนี้ก็มีเหตุผลอยู่เหมือนกัน เพราะตัวอย่างได้เลือกการทำงานแบบง่าย ๆ คือ มองหาค่าในคอลัมน์แรก แล้วส่งกลับค่าในคอลัมน์ทางขวามือ แต่ถ้าเกิดโจทย์มันไม่ได้ตรงไปตรงมาแบบนี้ล่ะ?
ข้อดีของ Index and Match
ยกตัวอย่างหนึ่ง ที่แสดงความยืดหยุ่นในการค้นหาของ index & match
สมมติว่า เราต้องการหาชื่อหน่วยที่ดินจำนวนหนึ่ง (คอลัมน์ F) อยู่ในระวางแผนที่ใด (คอลัมน์ A) ถ้าจะหาโดยใช้ vlookup จะต้องย้ายคอลัมน์ F ให้มาอยู่หน้าคอลัมน์ A แต่สำหรับ index & match ไม่จำเป็นต้องย้ายคอลัมน์ เราสามารถค้นหาจากขวามาซ้ายได้
ถ้าคิดว่าสูตรมันซับซ้อน เพราะต้องคิดหลายขั้น ลองแยกสูตรออกทีละส่วน
ลำดับแรก ใช้ match เพื่อแสดงลำดับของคำที่ต้องการหา ในตัวอย่าง เราจะเริ่มค้นหาจาก M2 (ถนนสิทธิประชาราษฎร์) สูตรเริ่มต้นหา match ก็คือ
MATCH(“ถนนสิทธิประชาราษฎร์”,$F$2:$F$2867,0))
นั่นคือ หาชื่อหน่วย “ถนนสิทธิประชาราษฎร์” ที่ตรงกับข้อมูลซึ่งอยู่ในcell F2 ถึง F2867 (ใส่ $ ครอบไว้เพื่อตรึงการอ้างอิงเพราะเดี่ยวจะสำเนาสูตรไปใช้กับ cell อื่นด้านล่างต่อไป ไม่ว่าจะ vlookup หรือ อะไรก็ตาม แนะนำให้ตรึงแถว/คอลัมน์ (ใส่ $ ครอบไว้) เพื่อไม่ให้การอ้างอิงตำแหน่งคลาดเคลื่อนไป) และกำหนดประเภทการแสดงผลเป็น 0 คือส่งค่าเฉพาะที่ตรงกันเท่านั้น สำหรับการทำงานที่ต้องอ้างอิงเพื่อค้นหา
จากนั้น เราจะเอา match ไปใช้กับ index นั่นคือ
INDEX($A$2:$A$2867, MATCH(“ถนนสิทธิประชาราษฎร์”,$F$2:$F$2867,0))
นั่นคือ ค้นหาจาก A2 ถึง A2867 ว่าลำดับใดที่ตรงกับ “ถนนสิทธิประชาราษฎร์” ในคอลัมน์ F ที่ได้จาก match
หมายเหตุ อย่าลืมว่า match จะแสดงค่าแรกที่เจอ อย่างในตัวอย่างนี้ถ้า “ถนนสิทธิประชาราษฎร์” มีมากกว่าหนึ่ง จะส่งผลค่าของตัวแรกที่พบ
ถ้าแยกแบบนี้แล้วจะนึกออกว่าขั้นตอนลำดับการค้นหาของ index & match เป็นอย่างไร หวังว่าพอจะทำความเข้าใจกับที่มาของการใช้งาน index & match บ้าง
ทำไมใช้ Index and Match แทน vlookup ?
ที่ผ่านมา เวลาทำงานจริงก็ใช้ทั้ง vlookup และ index & match แต่ระยะหลังจะใช้ index & match บ่อยกว่า vlookup ด้วยเหตุผลดังนี้
ข้อแรก จากประสบการณ์ส่วนตัว ถ้าใช้ข้อมูลตารางที่มีจำนวนแถวหลายพัน (หรือหลักแสนในหลายครั้ง) การใช้ index & match แสดงผลลัพธ์เร็วกว่า vlookup มาก อันนี้คือเรื่องสำคัญ เพราะบางครั้ง ใช้ vlookup แล้ว excel นิ่งไปเลย
ข้อสอง ถ้าหากข้อแรกเป็นประสบการณ์ส่วนตัวที่อาจจะไม่น่าเชื่อถือ (แต่ลองมาหลายครั้งแล้วจะเป็นแบบนี้ตลอด) ก็มาอีกข้อหนึ่งซึ่งเป็น fact หรือข้อเท็จจริงที่ว่า ถ้าใช่ vlookup ค่าที่ใช้ค้นหาจะต้องไม่เกิน 255 อักขระ (ปกติก็คงไม่มีใครหาเกินนี้?) ซึ่งถ้าใช้เกิน 255 อักขระจะได้ผลลัพธ์เป็น #Value! ซึ่งใน index & match จะไม่มีปัญหานี้ แต่จะว่าไป กรณีนี้ไม่ได้เกิดบ่อย ๆ
ข้อสาม ขอนำเสนอข้อดีของ Index and Match ที่ชอบมาก คือ ให้เราเลือกการค้นหาจากขวาไปซ้ายได้ ถ้าเป็น vlookup จะหาจากซ้ายไปขวาเท่านั้น ดังนั้นถ้าจะใช้ vlookup แต่คอลัมน์ข้อมูลที่ต้องการหาค่าดันอยู่ทางซ้ายของคอลัมน์ข้อมูลที่ใช้เป็นตัวค้นหา ก็ต้องปรับแต่งย้ายคอลัมน์ให้มันวุ่นวาย ซึ่งบางครั้ง (หลายครั้ง) ที่การจัดเรียงคอลัมน์จะกำหนดตายตัว เพื่อใช้กับงานอื่น การย้ายคอลัมน์แบบนี้จะทำให้ต้องย้ายไปย้ายกลับ วุ่นวาย ไม่สะดวกสำหรับ workflow
ข้อสี่ เราจะแทรกหรือตัดคอลัมน์ได้สะดวก ถ้าเป็น vlookup หากมีการแทรกหรือตัดคอลัมน์ จะทำให้สูตรคลาดเคลื่อนเพราะ table array ต้องเจาะจงเฉพาะ อย่างเช่น ถ้ากำหนดไว้ที่ A1:C10 แล้วต้องการผลลัพธ์จากคอลัมน์ C ก็ต้องใส่เลข 3 ในค่า col_index_num ของสูตร vlookup อย่างเช่น VLOOKUP(“ค่าที่ต้องการหา”, A1:C10, 3) ถ้าเกิดอยู่ ๆ มีใครแทรกคอลัมน์เข้ามาระหว่างคอลัมน์ A กับ C นั่นจะทำให้คอลัมน์เคลื่อน A1:C10 ก็ใช้ไม่ได้ ต้องเปลี่ยนเป็น A1:D10 และต้องเปลี่ยน 3 เป็น 4 ไม่ใช่นั้นจะได้ค่า error หรือไม่ก็คลาดเคลื่อนจากที่ต้องการ แต่ถ้าเป็น index & match จะลบหรือแทรกคอลัมน์ ก็จะไม่มีผลกระทบแบบนี้เพราะสูตรจะเปลี่ยนไปตามการอ้างอิง
ตัวอย่าง ถ้าลบคอลัมน์ไป 1 คอลัมน์ vlookup จะขึ้น REF!
ตัวอย่าง ถ้าเพิ่มคอลัมน์ การอ้างอิงใน vlookup จะคลาดเคลื่อน เพราะตำแหน่งเปลี่ยน แต่ vlookup ยังยืดตำแหน่งเดิม
จะเห็นว่า การใช้ฟังก์ชันซ้อนกันอาจดูยุ่งยาก แต่ถ้าเข้าใจระบบที่มาแล้วจะไม่ยากเลย และ Index and Match ยังยืดหยุ่นกว่า vlookup ในหลายด้าน
หากสนใจเรื่องว่าจะเลือกใช้ vlookup หรือ Index and Match โปรดลองอ่านเนื้อหาจากไมโครซอฟต์เรื่อง Look up values with VLOOKUP, INDEX, or MATCH ก่อน
ทั้งนี้ไม่ใช่ว่า vlookup ไม่ดี เพียงแค่นำเสนอตัวเลือกอีกอย่างไว้สำหรับทำงานเท่านั้น