ข้อมูลที่ลอกมาจากระบบอื่นที่ไม่ใช่ Excel มักมีโครงสร้างข้อมูลซึ่งไม่ได้เป็นฐานข้อมูลที่ดี โดยเฉพาะข้อมูลที่ export ออกมาจากรายงานทางบัญชีหรือการผลิต อาจมีตำแหน่งข้อมูลที่ต้องการคำตอบวางไว้ในแนวเยื้องกับค่าที่ใช้หา
สูตร VLookup สามารถใช้ค้นหาค่าคำตอบที่อยู่ในแนวเดียวกันกับค่าที่ใช้หาเท่านั้น หากคำตอบที่ต้องการอยู่ในเซลล์ที่อยู่เยื้องกันกับค่าที่ใช้หา จะไม่สามารถใช้สูตร VLookup แล้วถ้าหากคิดจะนำสูตร Match และ Index มาใช้ก็ต้องใช้ร่วมกับสูตรอื่น
ในภาพนี้มีข้อมูลถูกจัดเก็บไว้เป็นชุดๆ โดยแต่ละชุดมีขนาดความสูงความกว้างเท่ากัน ชุดแรก a มีพื้นที่ตั้งแต่เซลล์ B2:D5 ชุดที่สอง b มีพื้นที่ตั้งแต่เซลล์ B6:D9 ชุดที่สาม c มีพื้นที่ตั้งแต่เซลล์ B10:D13 ชุดที่สี่ d มีพื้นที่ตั้งแต่เซลล์ B14:D17 ซึ่งในอนาคตอาจเพิ่มข้อมูลชุดต่อไปด้านล่างลงไปเรื่อยๆ
สิ่งที่ต้องการก็คือ เมื่อใส่ค่า c ลงไปในเซลล์ H2 ให้หาว่าในชุด c มีตัวเลขบันทึกไว้ในเซลล์ด้านล่างขวามือสุดของชุด c เป็นเท่าใด ซึ่งคำตอบที่ต้องการคือตัวเลข 333 จากเซลล์ D13 และหากเปลี่ยนค่าในเซลล์ H2 เป็น a, b, c, หรือ d ก็ต้องหาตัวเลขคำตอบออกมาให้เป็น 111, 222, 333, 444 ตามลำดับด้วย
วิธีที่ 1
เริ่มจากการใช้สูตรในเซลล์ H5 =MATCH(H2,B:B,0) หาให้ได้ก่อนว่าค่า c ในเซลล์ H2 นั้นเมื่อดูจาก column B แล้วอยู่ใน row 10
เนื่องจากเรามีข้อสมมติฐานที่สำคัญว่าแต่ละชุดมีขนาดความสูงความกว้างเท่ากัน ในขั้นต่อไปให้วัดความสูงหรือจำนวน row ของชุดแรกด้วยสูตรในเซลล์ H6 =ROWS(B2:B5) ได้ความสูง 4 row
จากนั้นให้หาเลขที่ row ของ row สุดท้ายของชุด c โดยการนำเลขที่ row แรกบวกเพิ่มด้วยความสูงแล้วลบ 1 ด้วยสูตรในเซลล์ H7 =H5+H6-1 ได้ตำแหน่งของเลข 333 แล้วว่าอยู่ใน row 13
แล้วให้ใช้สูตรในเซลล์ H8 =INDEX(D:D,H7) เพื่อหาว่าจาก column D มีข้อมูลในลำดับที่ 13 จากเซลล์ H7 คือตัวเลขคำตอบ 333
แทนที่จะต้องเสียเวลาสร้างสูตรหลายขั้น อาจใช้สูตรต่อไปนี้หาคำตอบ 333 เลยก็ได้
=INDEX( D:D, MATCH(H2,B:B,0) + ROWS(B2:B5) – 1 )
วิธีที่ 2
ใช้สูตรต่อไปนี้ในเซลล์ H11 ลัดหาตัวเลขคำตอบ 333
=OFFSET( INDEX(B:B,MATCH(H2,B:B,0)), ROWS(B2:B5)-1, COLUMNS(B2:D2)-1)
อีกนัยหนึ่งคือสูตร =OFFSET( B10, 3, 2) เพื่อหาค่าที่อยู่ในตำแหน่งถัดไปจากเซลล์ B10 ถัดลงไป 3 row และถัดไปทางด้านขวา 2 column นั่นเอง
สังเกตว่าใช้สูตร MATCH(H2,B:B,0) เช่นเดียวกันกับวิธีแรก โดยเมื่อนำไปซ้อนในสูตร Index เป็นสูตร INDEX(B:B,MATCH(H2,B:B,0)) จะกลายเป็นสูตรที่บอกตำแหน่ง B10 ของข้อมูลในลำดับที่ 10 ใน column B
สูตร INDEX(B:B,MATCH(H2,B:B,0)) นี้ ถ้าสร้างลงในเซลล์จะคือค่าออกมาเป็นค่าในเซลล์ B10 คือให้คำตอบเป็น c แต่เมื่อนำสูตร INDEX(B:B,MATCH(H2,B:B,0)) ไปสร้างซ้อนลงในสูตร Offset จะเลิกหาค่าตัว c แต่จะคืนค่าคำตอบออกมาเป็นตำแหน่งเซลล์ B10 แทน
โปรดจำไว้ว่า สูตรใดที่สามารถใช้หาค่าได้โดยตรง สูตรนั้นย่อมกลายเป็นสูตรที่บอกตำแหน่งของค่าที่ใช้หาได้ ต่อเมื่อนำสูตรไปสร้างซ้อนลงไปในสูตรอื่น
ROWS(B2:B5)-1 ใช้หาว่าค่า 333 ที่ต้องการหานั้น อยู่ถัดจากเซลล์ B10 ลงไป 3 เซลล์ โดยหาจากขนาดความสูงที่หาจาก ROWS(B2:B5) ลบทิ้งเสีย 1 row
COLUMNS(B2:D2)-1 ใช้หาว่าค่า 333 ที่ต้องการหานั้น อยู่ถัดจากเซลล์ B10 ไปทางด้านขวา 2 เซลล์ โดยหาจากขนาดความสูงที่หาจาก COLUMNS(B2:D2) ลบทิ้งเสีย 1 column
วิธีที่ 3
วิธีนี้มีที่มาจากสูตร =Indirect( “ตำแหน่งเซลล์ที่ต้องการหาค่า”) ซึ่งจะคืนค่าออกมาเป็นค่าในเซลล์ที่ทราบตำแหน่ง ดังนั้นหากต้องการหาค่า 333 จากเซลล์ D13 ก็ต้องหาทางสร้างสูตร =Indirect(“D13”) ออกมาให้ได้
ขั้นแรกหาเลขที่ row 13 ได้จากสูตรในเซลล์ H15 =MATCH(H2,B:B,0)+ROWS(B2:B5)-1 ซึ่งได้จากการนำเลขที่ row 10 ที่ได้จากสูตร MATCH(H2,B:B,0) ไปบวกเพิ่มด้วยความสูง 4 row แล้วลบทิ้งเสีย 1 row
ต่อไปหาเลขที่ column 4 จากสูตรในเซลล์ H16 =COLUMN(D2)
เมื่อนำค่าจากเซลล์ H15 และ H16 ส่งต่อไปยังสูตร ADDRESS(H15,H16) ย่อมเทียบเท่ากับสูตร =INDIRECT(ADDRESS(13,4)) หรือ =INDIRECT("$D$13")
แทนที่ต้องใช้หลายสูตรหลายขั้นตอน จะใช้สูตรลัดนี้แทนก็ได้
=INDIRECT( ADDRESS( MATCH(H2,B:B,0) + ROWS(B2:B5) - 1, COLUMN(D2) ) )
วิธีที่ 4
ถ้าคำตอบที่ต้องการเป็นตัวเลข ปัญหานี้ใช้สูตร SumIF ง่ายๆแบบนี้ครับ คงไม่ค่อยเห็นโครงสร้างนี้กันใช่ไหม
=SumIF(B2:B1000,H2,D5)
ถ้าใช้ Excel 365 หรือรุ่นที่มี XLookup ใช้สูตรแบบนี้จะหาค่าได้ทั้งตัวเลขตัวอักษร
=XLOOKUP(H2,B2:B1000,D5:D1003)
