การค้นหาข้อมูลจากหลายไฟล์หลายชีตหลายคอลัมน์ด้วย Vlookup

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

ยกตัวอย่างข้อมูลของเราเป็นข้อมูล Product อยู่ใน 3 ไฟล์ แต่ละไฟล์จะมีข้อมูลอยู่ที่ Sheet2 – Sheet4 ประกอบด้วย Product Name, Quantity และ Amount โดยต้องการจะ Lookup ค่า Product ในเซลล์ A4 ของ Sheet1 ของไฟล์หลัก จาก Sheet2 – Sheet4 ของแต่ละไฟล์ โดยให้สามารถค้นหาได้ทุกคอลัมน์ หากค้นพบให้นำข้อมูล Quantity และ Amount มาวางที่ เซลล์ B4, C4 ของ Sheet1 ของไฟล์หลักตามลำดับ ซึ่งได้มีการกำหนดชื่อไฟล์และชื่อชีตที่จะดึงข้อมูลเอาไว้ล่วงหน้าที่เซลล์ H1:I9 ของ Sheet1 ของไฟล์หลัก

ภาพการใช้ Vlookup ดึงข้อมูลที่ไม่เป็นระเบียบจากหลายไฟล์ หลายชีต หลายคอลัมน์มาแสดง

ไฟล์หลัก

ไฟล์ที่ 1

ไฟล์ที่ 2

เราสามารถเขียนสูตรที่เซลล์ B4 ของ Sheet1 ของไฟล์หลัก เพื่อค้นหาค่า Quantity ได้ดังนี้

=VLOOKUP($A4,OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9&"!A2",
MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9&"!A2:A100"),
0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-ROW($I$1)+1)))),
0,MATCH(TRUE,COUNTIF(OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9
&"!A2:A100",MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9
&"!A2:A100"),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-
ROW($I$1)+1)))),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4)>0,0)-1,100,3),
COLUMNS($A3:B3),0)

กดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรไปที่ C4 เพื่อให้แสดงค่า Amount สามารถดาวน์โหลดไฟล์ด้านล่างไปศึกษาได้ตามสะดวกครับ 

Attachments