การค้นหาข้อมูลจากหลายชีตหลายคอลัมน์ที่ไม่เป็นระเบียบด้วย Vlookup

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

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

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

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

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

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