สูตร VLookup และสูตร Match มีจุดอ่อนตรงที่สูตรเหล่านี้จะทำงานกับข้อมูลที่ไม่ซ้ำเท่านั้น หากนำไปใช้กับตารางที่มีข้อมูลซ้ำจะให้คำตอบของข้อมูลรายการแรกที่ซ้ำกัน ส่วนคำสั่ง Data > Advanced หรือ Filter จะทำงานต่อเมื่อถูกสั่งใหม่เป็นครั้งๆไป ดังนั้นหากต้องการแสดงรายละเอียดของข้อมูลที่ซ้ำกันในทันทีที่มีการเปลี่ยนแปลง ต้องหันมารู้จักกับสูตรที่เรียกกันว่าสูตร Multiple Match
สูตร Multiple Match ถือเป็นสูตรที่ถูกถามกันบ่อยครั้งมาก ถึงแม้ได้เคยเขียนอธิบายไว้อย่างละเอียดหลายครั้งหลายโอกาสแล้วก็ตาม มักไม่เข้าใจว่าสูตรนี้สร้างกันได้อย่างไรหากไม่ได้เห็นขั้นตอนการสร้างทีละขั้นกับตาตัวเอง
ตารางด้านซ้ายมือของภาพนี้เป็นตารางฐานข้อมูลซึ่งมีค่าซ้ำกันบันทึกไว้ เช่น ข้อมูลของรหัส a001 ถูกบันทึกไว้ในรายการที่ 1, 6, 9, 11, และ 15 โดยตัวเลขตำแหน่งรายการของรหัส a001 เหล่านี้สามารถหาได้ด้วยสูตร Multiple Match {=SMALL(IF(ID=G3,Num),Num)} ซึ่งสร้างไว้ในเซลล์ L3:L12 แบบ Array
ตัวอย่างนี้ใช้การตั้งชื่อ Range Name ว่า Num, ID, Name, และ Amount ให้กับพื้นที่ข้อมูล B3:B20, C3:C20, D3:D20, และ E3:E20 ตามลำดับ ส่วนเซลล์ G3 เป็นเซลล์รับค่ารหัสที่ต้องการค้นหาตำแหน่งรายการ
เพื่อแสดงให้เห็นที่ไปที่มาของสูตร {=SMALL(IF(ID=G3,Num),Num)} ว่าเกิดจากการคำนวณหลายขั้นตอนและแต่ละขั้นทำหน้าที่หาค่าอะไรบ้าง ขออธิบายสูตรที่สร้างไว้ใน Step1 - Step3 ดังนี้ (เวลาใช้งานจริงไม่จำเป็นต้องสร้างสูตรเหล่านี้แต่อย่างใด)
Step1 : เริ่มจากเลือกเซลล์ I3:I20 พร้อมกันแล้วสร้างสูตร =ID=G3 แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=ID=G3} คืนค่าออกมาเป็น True คู่ขนานตรงกับตำแหน่งรายการที่มีรหัส a001
Step2 : ปรับตำแหน่งที่เป็น True จาก Step1 ให้เป็นเลขที่รายการ Num โดยเลือกเซลล์ J3:J20 พร้อมกันแล้วสร้างสูตร =IF(I3:I20,Num) แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=IF(I3:I20,Num)} จะพบว่าตำแหน่งที่เคยเป็นค่า True กลายเป็นเลขตำแหน่งรายการ 1, 6, 9, 11, 15 ขึ้นมาให้เห็นแทน
Step3 : นำตัวเลขตำแหน่งรายการจาก Step2 มาเรียงจากน้อยไปมากโดยนำไปซ้อนในสูตร Small โดยเลือกเซลล์ K3:K20 พร้อมกันแล้วสร้างสูตร =SMALL(J3:J20,Num) แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=SMALL(J3:J20,Num)}
เมื่อนำสูตรจาก Step1 – Step3 มาซ้อนกันจะกลายเป็นสูตร Multiple Match ในเซลล์ L3:L12 {=SMALL(IF(ID=G3,Num),Num)} ทั้งนี้โปรดสังเกตว่าไม่จำเป็นต้องสร้างสูตรลงไปในเซลล์ L3:L20 ที่ยาวลงมาถึง row 20 เช่นที่ใช้กับสูตรใน Step1 – Step3 แต่อย่างใด แต่ให้เลือกจำนวน row จากเซลล์ L3:L12 หรือ 10 row เท่าที่คิดว่ารหัสจะมีโอกาสซ้ำกันสูงสุดกี่ครั้งก็พอ จากนั้นให้ใช้สูตร Index ดึงข้อมูลตามลำดับรายการที่หาได้มาแสดงต่อไป
