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

สูตรที่เคยทำให้คนถอนหายใจ…วันนี้จะทำให้คุณยิ้มได้

“ข้อมูลกระจัดกระจายไม่เป็นระเบียบ? สูตรนี้จะพาคุณดึงข้อมูลกลับมาอย่างแม่นยำ ด้วยพลังของ VLOOKUP ที่ถูกปลุกให้มีชีวิต”

🧩 ปัญหาที่หลายคนเจอ

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

“จุดเริ่มต้นของการ lookup แบบข้ามไฟล์—ไฟล์หลักที่รอรับข้อมูลจากระบบที่ดูไม่เป็นระเบียบ แต่ถูกควบคุมด้วยสูตรที่มีโครงสร้าง”

🧠 แนวคิดของสูตร

• INDIRECT เพื่อเรียกข้อมูลจากหลายไฟล์/ชีตแบบ dynamic
• OFFSET เพื่อเลื่อนช่วงข้อมูลไปหาคอลัมน์ที่มีข้อมูล
• VLOOKUP เพื่อดึงข้อมูลจากคอลัมน์ที่ต้องการ

“ข้อมูลต้นทางจากไฟล์แรก—แม้จะเยื้องทั้งแนวแถวและคอลัมน์ แต่สูตรจะนำค่าที่ต้องการกลับมาอย่างแม่นยำ”

“อีกหนึ่งชีตต้นทางที่แสดงความหลากหลายของโครงสร้างข้อมูล—แต่สูตรเดียวกันสามารถค้นหาและดึงค่ากลับมาได้อย่างมั่นคง”

🪜 สูตรเต็ม (สำหรับเซลล์ B4)

=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

🧪 คำอธิบายโครงสร้างสูตร

ส่วนของสูตร	ความหมาย
$A4	ค่าที่ต้องการค้นหา เช่น รหัสสินค้า
$H$1:$H$9	รายชื่อไฟล์ต้นทาง เช่น "[File1.xlsx]"
$I$1:$I$9	รายชื่อชีตในแต่ละไฟล์ เช่น "Sheet2"
INDIRECT(...)	เรียกช่วงข้อมูลจากไฟล์และชีตแบบ Dynamic
OFFSET(..., COLUMN(...)-COLUMN(...))	เลื่อนช่วงข้อมูลเพื่อค้นหาทุกคอลัมน์
COUNTIF(...,$A4)	ตรวจสอบว่าค่าที่ต้องการมีอยู่ในช่วงใด
MATCH(TRUE, COUNTIF(...))	หาตำแหน่งคอลัมน์ที่พบข้อมูล
INDEX(..., MIN(IF(...)))	เลือกไฟล์และชีตที่พบข้อมูลก่อน
VLOOKUP(..., COLUMNS(...), 0)	ดึงข้อมูลจากคอลัมน์ที่ต้องการแบบ Exact Match