แม้สูตร Match สามารถหาตำแหน่งแรกของค่าที่ต้องการ เช่น ใช้ค้นหาตำแหน่งของรหัส แต่จะได้เฉพาะตำแหน่งแรกของรหัส ซึ่งหากมีรหัสซ้ำกันอยู่หลายรายการ สูตร Match จะคืนค่าเป็นเลขเฉพาะของรหัสลำดับแรกที่บันทึกอยู่เท่านั้น จึงเป็นข้อจำกัดทำให้เราไม่สามารถใช้สูตร Match ค้นหารายการอื่นๆที่ซ้ำกัน
หากใช้เมนูคำสั่ง Data > Filter > AutoFilter สามารถใช้กรองหารายการซึ่งมีรายการซ้ำกันอยู่ได้ แต่ทุกครั้งซึ่งต้องการกรองหารหัสอื่นๆก็ต้องสั่ง AutoFilter เองใหม่ทุกครั้ง อีกทั้ง Excel ไม่สามารถกรองหาค่าใหม่ให้โดยอัตโนมัติ นอกจากนี้ AutoFilter ยังสามารถแสดง Unique Item ได้สูงสุด 999 Item และภายใน Sheet หนึ่งจะใช้ AutoFilter ได้ในตารางเดียวที่ติดต่อกันเท่านั้น
ส่วนเมนูคำสั่ง Data > Filter > Advanced Filter มีข้อจำกัดในส่วนของ
ทั้ง AutoFilter และ Advanced Filter เมื่อกรองหาค่าที่ต้องการได้แล้ว เราจะต้อง Copy ผลออกไปใช้เอง และต้องสั่งใหม่เองทุกครั้งที่มีเงื่อนไขการกรองต่างไปจากเดิม
Excel มีสูตรสำหรับจัดการฐานข้อมูลโดยเฉพาะ ซึ่งสูตรกลุ่มนี้จะมีอักษร D นำหน้าสูตร เช่น DSum, DCount, DAverage, DMax, DMin เป็นต้น มีรูปแบบการใช้สูตร =ชื่อสูตร(ตารางข้อมูล, เลขที่ Field คำตอบ, ตารางเงื่อนไข) สามารถใช้หายอดตัวเลขได้ง่าย แต่มีข้อจำกัดจากตาราง Criteria ซึ่งต้องมีเงื่อนไขกำหนดไว้ติดต่อกันเสมอ
ด้วยเหตุที่เมนูคำสั่งและสูตรสำเร็จรูปที่ Excel มีอยู่มีข้อจำกัดในการใช้งานอยู่บ้าง จึงขอแนะนำให้ใช้สูตร Array ช่วยในการค้นหารายการที่ซ้ำกัน โดยมีขั้นตอนดังนี้
Step 1 : หาเลขตำแหน่งของรหัส ID
เริ่มจากเลือกพื้นที่เซลล์ตามแนวตั้งให้มีจำนวนเซลล์พอเพียงกับจำนวนรายการซึ่งมีโอกาสซ้ำกันมากที่สุด ในที่นี้คือเซลล์ G4:G13 แล้วสร้างสูตร Array โดยกด Ctrl+Shift+Enter แทน Enter
= IF ( ID=IDChoice, Num )
เมื่อกำหนดให้ IDChoice เป็นรหัส a001
Step 2 : จัดเรียงเลขตำแหน่งของรหัส ID จากน้อยไปมาก
สร้างสูตร Array เช่นเดียวกัน Step 1 แต่ใช้สูตร Small ซ้อนเข้าไปด้านหน้า เพื่อจัดเรียงเลขตำแหน่งที่ได้จาก Step 1 จากน้อยไปหามาก โดยใช้สูตร Array
= SMALL ( Step1, Num )
หรือใช้สูตร Array แบบเต็ม (Mega Formula)
= SMALL ( IF ( ID=IDChoice, Num ), Num )
กรณีที่ไม่ต้องการใช้ Range Num แต่ต้องการใช้สูตรสร้างเลขลำดับให้เอง ให้ใช้สูตร = Row ( Indirect( "เลขที่เริ่ม : เลขที่สุดท้าย" ) ) แทนส่วนที่อ้างอิงถึง Num จะได้สูตร Array แบบ Advance ทั้งนี้ให้ใช้สูตร Rows เพื่อนับจำนวนบรรทัดของตารางเก็บข้อมูลใช้เป็นตัวเลขสุดท้าย
= SMALL(IF(ID=IDChoice,
ROW(INDIRECT("1:"&ROWS(ID)))),
ROW(INDIRECT("1:"&ROWS(ID))))
ผลจาก Step 2 ทำให้ได้เลขลำดับตำแหน่งของรายการที่ซ้ำกันอยู่ จากนั้นจึงใช้สูตร Index ค้นหารายละเอียดของรายการลำดับนั้นต่อไป โดยใช้สูตร
= IF ( ISERROR(Advance), 0, INDEX ( Name,Advance,1 ) )
= IF ( ISERROR(Advance), 0, INDEX ( Amount,Advance, 1) )
สาเหตุที่ใช้สูตร IsError ซ้อนเข้าไปในสูตร IF เพื่อช่วยให้ใช้สูตร Index ค้นหาค่าต่อเมื่อเลขลำดับของตำแหน่งจาก Advance ไม่ error เท่านั้น