INDEX MATCH Functions ดีกว่า VLOOKUP Function อย่างไร

บทความก่อนหน้านี้ เรานำเสนอทั้งการทำงานและตัวอย่างของ Vlookup Function / Index Function และ Match Function ซึ่งถ้าคุณสามารถทบทวน vlookup Function ได้ที่ "การค้นหาข้อมูลด้วย vLookUp จาก Excel" ส่วน Index Function หรือ Match Function ได้ที่ "ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function" 

ในบทความนี้ เรามาดูข้อดีต่างๆ ของการนำ Index&Match มาใช้ร่วมกัน แทน Vlookup ดีกว่า 

ข้อดีของการใช้ INDEX ร่วมกับ MATCH แทนการใช้ VLOOKUP 
1. สามารถแทรกหรือลบคอลัมน์ได้ภายหลัง เมื่อคุณใช้ Index Match ในการทำงานแทน Vlookup คุณสามารถแทรกคอลัมน์ระหว่าง(คอลัมน์เริ่มต้นของตาราง จนถึึง คอลัมน์ที่คุณนำมาใช้อ้างอิงในสูตร) แต่ ✋ถ้าคุณใช้ Vlookup ในสูตร คุณจะไม่สามารถแทรกคอลัมน์ได้ เพราะสูตรที่คุณเขียนไว้มันจะ Error ทันที 

รูปที่ 1 ตัวอย่างที่ใช้เปรียบเทียบระหว่าง Index&Match และ Vlookup

ตัวอย่าง การค้นหาข้อมูลในเซลล์ด้วย Index&Match จากรูปที่ 1 เราได้เขียนสูตรด้วย Index&Match ในคอลัมน์ C และ D เพื่อค้นหาข้อมูลสาขาและรายได้ของแต่ละร้าน จากตารางสีฟ้า 
ได้สูตรในคอลัมน์ C ดังนี้ 

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($C$1,$F$1:$I$1,0))

จากสูตรข้างบนนี้ สามารถทำงานได้คำตอบเดียวกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน A2 (ค่าในเซลล์ คือ s001) มาค้นหาในช่วง F2:F14 (ค่าในเซลล์ คือ s014, s009, s002, s001, s008, s003, s015, s012, s004, s005, s006, s013, s010) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 4

ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Branch) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 2 

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 2 ดังรูปที่ 2

รูปที่ 2 แสดงผลลัพธ์ของการใช้ Index&Match 

ได้สูตรในคอลัมน์ D ดังนี้

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($D$1,$F$1:$I$1,0))

จากสูตรนี้ สามารถทำงานได้คำตอบเหมือนกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 เขียนเหมือน argument ตัวที่ 2 ในคอลัมน์ C


ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Income) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 3

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 3 ดังรูปที่ 2

หลังจากนั้น ทำการแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า) ดังรูปที่ 3

รูปที่ 3 แสดงตัวอย่างเมื่อแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า)

คุณจะเห็นได้ว่าสูตรในคอลัมน์ C มีการเปลี่ยนเซลล์อ้างอิงทำให้คำตอบของสูตรที่ใช้ Index&Match Function ยังคงได้คำตอบเดิม ทำให้เราไม่ต้องแก้ไขสูตรนั้นเอง ซึ่งถ้าคุณทำการค้นหาคอลัมน์ C และ D ด้วย Vlookup Function เมื่อคุณแทรกคอลัมน์แบบเดียวกับรูปที่ 3 คุณต้องกลับมาแก้ไขสูตรด้วย 

✻✼✽✻✼✽✻✼✽

2. สามารถค้นหาข้อมูลจากทิศทางใดก็ได้ เมื่อคุณต้องการค้นหาข้อมูลที่อยู่ทางด้านซ้ายของตารางอ้างอิง คุณจะไม่สามารถใช้ Vlookup ค้นหาได้ ดังนั้น ต้องใช้ Index&Match เข้ามาแทน

ตัวอย่าง จากรูปที่ 1 ที่ตารางสีขาว ให้ทำการค้นหา "ชื่อร้าน" (Name Shop) จากสาขา (Branch) "Lampang" ซึ่งแบบนี้จะใช้ vlookup ไม่ได้ เพราะชื่อร้าน (คอลัมน์ที่ค้นหา) อยู่ทางซ้ายของสาขา (คอลัมน์อ้างอิง) เมื่อคุณเขียนสูตรด้วย Index&Match สามารถเขียนได้ ดังนี้

=INDEX($A$2:$D$14,MATCH("Lampang",C2:C14,0),MATCH("NameShop",A1:D1,0))

จากสูตรนี้ ส่วนของ index function ประกอบด้วย 3 arguments (แบ่งด้วยสีเป็น 3 สี เพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 
โดย argument ตัวที่ 1 (สีส้ม) เป็นการกำหนดช่วงหรือตารางที่ทำการค้นหาข้อมูลมาแสดง

ส่วน argument ตัวที่ 2 (สีน้ำเงิน) เป็นการกำหนดเลขแถวให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ Lampang ไว้ ผลลัมน์ที่ได้ คือ 6

ส่วน argument ตัวที่ 3 (สีม่วง) เป็นการกำหนดเลขคอลัมน์ให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ NameShop ไว้ ผลลัมน์ที่ได้ คือ 2

✻✼✽✻✼✽✻✼✽

3. สามารถค้นหาค่าประมาณการได้ ในฟังก์ชั่น vlookup มีส่วนของการกำหนดวิธีการค้นหาได้ 2 แบบ คือ ค่าที่ตรงกัน (Exact Match) และ ค่าโดยประมาณ (Approximate Match) ซึ่งในตัวอย่างที่ผ่านมา เราได้อธิบายข้อดีที่ใช้วิธีการค้นหาแบบค่าที่ตรงกัน (Exact Match) แต่ในข้อดีข้อนี้ เราจะอธิบายข้อดีที่ใช้วิธีการค้นหาค่าโดยประมาณ (Approximate Match) นั้นเอง

ตัวอย่าง ค้นหาเกรด โดยให้วิธีการค้นหาเป็นแบบประมาณ (Approximate Match) ดังรูปที่ 4

รูปที่ 4 แสดงตัวอย่างการหาเกรดด้วย Index&Match

จากรูปนี้ เราเรียงคะแนนจากมากลงมาน้อย และคะแนนสูงสุดเราใส่ค่าที่เป็นคะแนนมากที่สุดที่ผู้สอบสามารถจะทำได้ เขียนสูตรได้ ดังนี้ 

=INDEX($B$2:$B$9,MATCH("99",$A$2:$A$9,-1))

จากสูตรนี้เราใช้ Index Function ที่มี 2 arguments (แบ่งด้วยสี 2 สี ตามลำดับ) โดย argument ที่ 1 (สีส้ม) ใส่ช่วงที่ต้องการค้นหา ซึ่งในที่นี้ต้องการค้นหาเกรด เราจึงใส่ B2:B9

ส่วน argument ที่ 2 เราใช้ Match Function เข้ามาช่วยในการค้นหาเลขของเซลล์ 

โดยใน Match Function มี 3 arguments ซึ่งใน argument ที่ 1 เรากำหนด "99" ลงไป, ใน argument ที่ 2 เรากำหนดช่วงที่จะเข้าไปค้นหา "A2:A9", ใน argument ที่ 3 เรากำหนดวิธีในการค้นหาเป็นแบบประมาณ (Approximate Match) โดยในตัวอย่างนี้ กำหนดเป็น -1 ซึ่งหมายถึง หาค่าที่มากกว่าหรือเท่ากับ นั้นเอง

ปล. ถ้าในรูปที่ 4 เป็นการเรียงคะแนนจากน้อยไปมาก การเขียนสูตรต้องกำหนดวิธีการค้นหาใน Match Function เป็น "1" หมายถึง หาค่าที่น้อยกว่าหรือเท่ากับ นั้นเอง

✻✼✽✻✼✽✻✼✽

4. ไม่ต้องนับคอลัมน์เพื่อระบุเลขคอลัมน์ที่ต้องดึงข้อมูลมาแสดงอีก เมื่อคุณใช้ Index&Match แทน Vlookup คุณไม่ต้องสนใจว่าค่าที่เราค้นหานั้นอยู่เลขคอลัมน์ใดอีก เพราะคุณสมบัติของ Match Function เข้ามาช่วยคุณแล้ว 

Read More

ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function

บทความนี้ จะอธิบายข้อดีของ Index และ Match ที่แก้ไข จุดด้อยของ Vlookup ได้ หากใครยังไม่รู้จักการทำงานพื้นฐานของฟังก์ชั่น Vlookup สามารถกลับไปอ่าน "การค้นหาข้อมูลด้วย vLookUp จาก Excel" และ "HLookup ช่วยค้นหาข้อมูลได้ต่างกับ VLookup อย่างไร" หรือต้องการทบทวนฟังก์ชั่น Match ก็สามารถอ่าน "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" เพื่อทบทวนได้ค่ะ

INDEX Function 
        หมายถึง การค้นหาค่า จากตารางอ้างอิง ตามแถวหรือคอลัมน์ที่กำหนด หรือ ใช้แทน VLOOKUP ได้ 

ไวยากรณ์ =INDEX(array, row_num, [column_num])

หลักการใช้ฟังก์ชั่น INDEX

• Array คือ ช่วงของเซลล์ที่ต้องการค้นหา
• row_num คือ เลขแถวใน Array 
• [column_num] คือ เลขคอลัมน์ใน Array 

เมื่อนำ INDEX มาใช้ร่วมกับ MATCH จะทำให้สามารถค้นหาข้อมูลได้ โดยการหาตำแหน่งด้วย MATCH และ นำมาแสดงด้วย INDEX ซึ่งจะเทียบได้กับทั้ง 2 ฟังก์ชั่นนี้ ทำหน้าที่แทน VLOOKUP นั้นเอง

ตัวอย่างการใช้ฟังก์ชั่น INDEX
        จากบทความนี้ เราจะใช้ตัวอย่างเดียวกับฟังก์ชั่น VLOOKUP และ MATCH เพื่อให้คุณ ๆ เห็นความแตกต่างระหว่างทั้ง 3 ฟังก์ชั่น 

รูปที่ 1 ตารางที่ใช้แสดงตัวอย่างการนำฟังก์ชั่น INDEX และ MATCH มาทำงาน

จากรูปที่ 1 ถ้าเราใช้ฟังก์ชั่น INDEX ในการค้นหา จะคืนค่าภายในเซลล์นั้นออกมา เช่น ต้องการค้นแถวที่ 10 และคอลัมน์ที่ 2 ก็ต้องเขียนว่า =INDEX($A$1:$D$14,10,2) ผลลัพธ์ที่ได้คือ "Charming" นั้นเอง

ข้อควรระวัง !!! 
ถ้าคุณเขียน =INDEX($A$2:$D$14,10,2) จะเห็นว่าช่วงของเซลล์ที่ต้องการค้นหาเริ่มต้นไม่เหมือนกัน (A2:D14) ทำให้ผลลัพธ์ที่ได้ผิดไปจากเดิม คือ "Cupid" 

หลักการใช้ฟังก์ชั่น Index นี้มันดูง่าย ๆ ไม่ซับซ้อนอะไรเลยถูกไหมคะ 😉😉😊😊

แต่ในความเป็นจริง คุณไม่มีทางรู้ว่า Data ที่กำลังหาอยู่ในแถวหรือคอลัมน์ที่เท่าไรแน่นอน ดังนั้น เราจึงควรทำความรู้จักกับ คุณลักษณะของฟังก์ชั่น MATCH ไว้ด้วย 

คุณสามารถกลับไปทบทวนหลักการของ Match Function ได้ที่บทความ "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" 

ตัวอย่างการใช้ฟังก์ชั่น INDEX ร่วมกับ MATCH แบบที่ 1
        ต้องการค้นหารายได้ (Income) ของร้าน Cupid โดยให้คุณเขียนสูตรจากทั้ง 2 ฟังก์ชั่น คือ Index และ Match ร่วมกัน เพื่อค้นหาค่า (Value) แทนฟังก์ชั่น VLOOKUP ซึ่งสามารถเขียนได้ ดังนี้  

=INDEX($A$2:$D$14,MATCH("Cupid",$B$2:$B$14,0),MATCH("Income",$A$1:$D$1,0))

จากสูตรด้านบนนี้ ถ้าคุณสังเกตดีๆ เรายังใช้ฟังก์ชั่น Index อยู่นะค่ะ แต่ส่วนของ Argument ที่ให้ระบุเลขแถว และเลขคอลัมน์ ของ Index เราได้ใช้ฟังก์ชั่น Match เข้ามาช่วยนั้นเอง 

ซึ่ง MATCH("Cupid",$B$2:$B$14,0) ใช้ในการค้นหาเลขลำดับของข้อมูล โดยเมื่ออ่านความหมายของสูตรนี้ คือ ค้นหาคำว่า Cupid จากลำดับ B2:B14 (แถว) และวิธีหา Cupid ต้องคำตรงกัน ดังนั้น ผลลัพธ์ คือ แถวลำดับที่10 (จุดสังเกต ใครรู้สึกว่าต้องเป็น ลำดับที่ 11 ไหม ซึ่งจะไม่ถูกต้อง เพราะ สูตรที่เขียนกำหนดช่วงเป็น B2:B14 เมื่อนับแต่ละแถวลงมา Cupid จะอยู่ลำดับที่ 10 พอดีค่ะ)

ปล. ให้คุณนึกว่าถ้ามีข้อมูลใน Excel จำนวนมากเป็นพัน ๆ แถว/คอลัมน์ เราคงไม่รู้ว่า สิ่งที่เราค้นหามันอยู่ในเลขแถวหรือเลขคอลัมน์อะไรแน่นอน ดังนั้น ฟังก์ชั่น Match จึงกลายมาเป็นฮีโรของ Index ทันที 

ตัวอย่างการใช้ฟังก์ชั่น Index ร่วมกับ Match แบบที่ 2 
        เราจะใช้โจทย์เดิมตามแบบที่ 1 แต่คุณ ๆ สามารถเขียนได้ ดังนี้

=INDEX($D$2:$D$14,MATCH("Cupid",$B$2:$B$14,0))

ในตัวอย่างแบบที่ 2 นี้ เราใช้ Argument เพียงระบุเลขแถวอย่างเดียวได้ และ ช่วงของเซลล์ที่ต้องการค้นหา ให้ระบุช่วงของสิ่งที่ต้องการให้แสดงผลลัพธ์ นั้นก็คือ คอลัมน์รายได้ (Income) นั้นเอง 

ปล. ทั้งตัวอย่างแบบที่ 1-2 จะได้ผลลัพธ์เหมือนกัน คือ 150,000 บาท เหมือนกันเลย

Read More