การนำข้อมูลมาแสดงยังปลายทางโดยมีเงื่อนไขเดียว ยกตัวอย่างเงื่อนไขเช่น หากมีค่าที่เราต้องการค้นหาอยู่ในคอลัมน์ใดของตาราง ให้นำคอลัมน์ที่เกี่ยวข้องกันมาแสดง เราสามารถใช้ Vlookup, Lookup หรือ Index และ Match ทำงานร่วมกันแบบธรรมดามาแสดงข้อมูลที่เกี่ยวข้องยังปลายทางได้
แต่หากมีหลายเงื่อนไขการค้นหาย่อมจะยุ่งยากขึ้น แนวทางหนึ่งที่สามารถนำมาหาคำตอบได้คือการใช้ Index และ Match แบบ Array เข้ามาช่วยได้ ซึ่งจะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter
ยกตัวอย่างภาพและสูตรด้านล่าง
เงื่อนไขคือ หาก Region มีค่าเป็น N และ Line มีค่าเป็น 1 แล้วให้นำ Product มาแสดง
สูตรที่ D2 คือ
=INDEX('Sheet2'!B2:B5,MATCH(1,(A2='Sheet2'!A2:A5)*(C2='Sheet2'!C2:C5),0))
ส่วนที่เป็น (A2='Sheet2'!A2:A5)*(C2='Sheet2'!C2:C5) คือเงื่อนไขในการค้นหาข้อมูล เป็นการ Lookup 2 เงื่อนไข สูตรลักษณะนี้จะต้องทำเป็น Array เท่านั้นจึงจะให้คำตอบได้ถูกต้อง
จากสูตรด้านบน A2='Sheet2'!A2:A5 เป็นการเปรียบเทียบดังนี้
A2='Sheet2'!A2
A2='Sheet2'!A3
A2='Sheet2'!A4
A2='Sheet2'!A5
เมื่อแทนค่าเข้าไปในสูตรจะได้เป็น
"N"="N"
"N"="W"
"N"="S"
"N"="E"
หากมีค่าเท่ากันผลลัพธ์จะได้ True หากไม่เท่าจะได้ False ผลลัพธ์จะได้เป็น
{True,False,False,False}
จากสูตร C2='Sheet2'!C2:C5 จะเป็นการเปรียบเทียบดังนี้
C2='Sheet2'!C2
C2='Sheet2'!C3
C2='Sheet2'!C4
C2='Sheet2'!C5
เมื่อแทนค่าเข้าไปในสูตรจะได้เป็น
1=1
1=1
1=1
1=1
หากมีค่าเท่ากันผลลัพธ์จะได้ True หากไม่เท่าจะได้ False ผลลัพธ์จะได้เป็น
{True,True,True,True}
ภาพของสูตรโดยรวมจะได้เป็น
=INDEX('Sheet2'!B2:B5,MATCH(1,{True,False,False,False}*{True,True,True,True},0))
จาก {True,False,False,False}*{True,True,True,True} โปรแกรมจะนำส่วนประกอบในตำแหน่งเดียวกันของ Array ชุดหน้าและชุดหลังมาคูณกัน หรือนั่นคือ
{True*True,False*True,False*True,False*True}
ผลลัพธ์จะได้ {1,0,0,0}
ภาพรวมของสูตรจะได้เป็น
=INDEX('Sheet2'!B2:B5,MATCH(1,{1,0,0,0},0))
จาก MATCH(1,{1,0,0,0},0) ผลลัพธ์ได้ 1 หมายถึงพบเลข 1 เป็นลำดับที่ 1 ในชุดข้อมูล {1,0,0,0}
ภาพรวมของสูตรจะได้เป็น
=INDEX('Sheet2'!B2:B5,1)
หมายถึงจากช่วง 'Sheet2'!B2:B5 ให้นำตัวที่ 1 มาแสดง คำตอบคือ Pd-01
แต่หากสูตรเป็น MATCH(1,{0,0,1,0},0) ผลลัพธ์ได้ 3 หมายถึงพบเลข 1 เป็นลำดับที่ 3 ในชุดข้อมูล {0,0,1,0}
ภาพรวมสูตรจะได้เป็น
=INDEX('Sheet2'!B2:B5,3)
หมายถึงจากช่วง 'Sheet2'!B2:B5 ให้นำตัวที่ 3 มาแสดง เช่นนี้เป็นต้น
ปกติช่วงเงื่อนไขควรจะเป็นการใช้ If แบบซ้อนกันตามจำนวนเงื่อนไขแทนการคูณเพื่อประสิทธิภาพและความรวดเร็วในการคำนวณ แต่เพื่อให้สะดวกต่อการอธิบายและสะดวกต่อการทำความเข้าใจของผู้อ่าน บทความนี้จึงขอยกตัวอย่างการใช้เงื่อนไขแบบคูณมาอธิบายเป็นเบื้องต้น
กรณีมีปัญหาการใช้งาน Excel and VBA สามารถสอบถามได้ที่ snasui.com